<!DOCTYPE html>
<html lang="zh-CN,en,zh-TW,default">
    <head hexo-theme='https://github.com/volantis-x/hexo-theme-volantis/tree/5.3.2'>
  <meta name="generator" content="Hexo 6.2.0">
  <meta name="Volantis" content="5.3.2">
  <meta charset="utf-8">
  <!-- SEO相关 -->
  
  <link rel="canonical" href="https://blog.bosong.online/clickhouse常用函数.html"/>
  <!-- 渲染优化 -->
    <meta http-equiv='x-dns-prefetch-control' content='on' />
      <link rel='dns-prefetch' href='https://npm.elemecdn.com'>
      <link rel="preconnect" href="https://npm.elemecdn.com" crossorigin>
  <meta name="renderer" content="webkit">
  <meta name="force-rendering" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
    <meta http-equiv="Content-Security-Policy" content=" default-src 'self' https:; block-all-mixed-content; base-uri 'self' https:; form-action 'self' https:; worker-src 'self' https:; connect-src 'self' https: *; img-src 'self' data: https: *; media-src 'self' https: *; font-src 'self' data: https: *; frame-src 'self' https: *; manifest-src 'self' https: *; child-src https:; script-src 'self' https: 'unsafe-inline' *; style-src 'self' https: 'unsafe-inline' *; ">
  <meta name="HandheldFriendly" content="True" >
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=5">
  <meta content="black-translucent" name="apple-mobile-web-app-status-bar-style">
  <meta content="telephone=no" name="format-detection">
  <!-- import head_begin begin -->
      <link rel="icon" type="image/png" sizes="16x16" href="https://imagecdn.bosong.online/image/google_16.png">
      <link rel="icon" type="image/png" sizes="32x32" href="https://imagecdn.bosong.online/image/google_32.png">
      <link rel="mask-icon" href="https://imagecdn.bosong.online/image/google.svg" color="#222">
  <!-- import head_begin end -->
  <!-- Custom Files headBegin begin-->
  
  <!-- Custom Files headBegin end-->
  <link rel="preload" href="/css/style.c5d207c8.css" as="style">
  <link rel="preload" href="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/fonts/VarelaRound/VarelaRound-Regular.ttf" as="font" type="font/ttf" crossorigin="anonymous">
<link rel="preload" href="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/fonts/UbuntuMono/UbuntuMono-Regular.ttf" as="font" type="font/ttf" crossorigin="anonymous">

  <!-- feed -->
      <link rel="alternate" href="/atom.xml" title="星空" type="application/atom+xml">
  <!-- 页面元数据 -->
  <title>ClickHouse常用函数 - 星空</title>
  <meta name="keywords" content="ClickHouse,Devops 技术人生 Java Golang 微服务 云计算">
  <meta desc name="description" content="前言近期项目引入了Clickhouse进行数据计算与展示，今天突然我们的PM同学来问我一个问题，怎么快速获取这十个品牌中GMV各自排名前三的SPU数据。当时想了想，一般情况下这种需求都是使用代码实现的，简直So easy，但是一旦写代码的话就没法快速获取并且以后类似的需求也没法快速响应了。
然后想到了咱们使用的是... - Jack - 星空">
  
<meta property="og:type" content="article">
<meta property="og:title" content="ClickHouse常用函数">
<meta property="og:url" content="https://blog.bosong.online/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html">
<meta property="og:site_name" content="星空">
<meta property="og:description" content="前言近期项目引入了Clickhouse进行数据计算与展示，今天突然我们的PM同学来问我一个问题，怎么快速获取这十个品牌中GMV各自排名前三的SPU数据。当时想了想，一般情况下这种需求都是使用代码实现的，简直So easy，但是一旦写代码的话就没法快速获取并且以后类似的需求也没法快速响应了。 然后想到了咱们使用的是Clickhouse数据库，它自带很多函数，在经过一段时间的查询以后，发现有一个函数还">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/blog/favicon/android-chrome-192x192.png">
<meta property="article:published_time" content="2021-03-18T12:47:45.000Z">
<meta property="article:modified_time" content="2022-06-02T01:05:59.611Z">
<meta property="article:author" content="Jack">
<meta property="article:tag" content="ClickHouse">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/blog/favicon/android-chrome-192x192.png">
  <style>
    /* 首屏样式 */
    #safearea {
  display: none;
}
/*
  * Workaround for Chrome bug, part 1
  * Chunk rendering for all but the first article.
  * /layout/_partial/scripts/content-visibility-scroll-fix.ejs
*/
.post-story + .post-story {
  content-visibility: auto;
  contain-intrinsic-size: 10px 500px;
}
:root {
  --color-site-body: #fff;
  --color-site-bg: #fff;
  --color-site-inner: #03a9f4;
  --color-site-footer: #666;
  --color-card: #fff;
  --color-text: #444;
  --color-block: #f6f6f6;
  --color-inlinecode: #c74f00;
  --color-codeblock: #fff7ea;
  --color-h1: #3a3a3a;
  --color-h2: #3a3a3a;
  --color-h3: #333;
  --color-h4: #444;
  --color-h5: #555;
  --color-h6: #666;
  --color-p: #444;
  --color-list: #666;
  --color-list-hl: #0287c3;
  --color-meta: #888;
  --color-read-bkg: #e0d8c8;
  --color-read-post: #f8f1e2;
}
* {
  box-sizing: border-box;
  -webkit-box-sizing: border-box;
  -moz-box-sizing: border-box;
  outline: none;
  margin: 0;
  padding: 0;
}
*::-webkit-scrollbar {
  height: 4px;
  width: 4px;
}
*::-webkit-scrollbar-track-piece {
  background: transparent;
}
*::-webkit-scrollbar-thumb {
  background: #03a9f4;
  cursor: pointer;
  border-radius: 2px;
  -webkit-border-radius: 2px;
}
*::-webkit-scrollbar-thumb:hover {
  background: #ff5722;
}
html {
  color: var(--color-text);
  width: 100%;
  height: 100%;
  font-family: UbuntuMono, "Varela Round", "PingFang SC", "Microsoft YaHei", Helvetica, Arial, Menlo, Monaco, monospace, sans-serif;
  font-size: 16px;
}
html >::-webkit-scrollbar {
  height: 4px;
  width: 4px;
}
html >::-webkit-scrollbar-track-piece {
  background: transparent;
}
html >::-webkit-scrollbar-thumb {
  background: #03a9f4;
  cursor: pointer;
  border-radius: 2px;
  -webkit-border-radius: 2px;
}
html >::-webkit-scrollbar-thumb:hover {
  background: #ff5722;
}
body {
  background-color: var(--color-site-body);
  text-rendering: optimizelegibility;
  -webkit-tap-highlight-color: rgba(0,0,0,0);
  line-height: 1.6;
  -webkit-text-size-adjust: 100%;
  -ms-text-size-adjust: 100%;
}
body.modal-active {
  overflow: hidden;
}
@media screen and (max-width: 680px) {
  body.modal-active {
    position: fixed;
    top: 0;
    right: 0;
    bottom: 0;
    left: 0;
  }
}
a {
  color: #2196f3;
  cursor: pointer;
  text-decoration: none;
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
}
a:hover {
  color: #ff5722;
}
a:active,
a:hover {
  outline: 0;
}
ul,
ol {
  padding-left: 0;
}
ul li,
ol li {
  list-style: none;
}
header {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
}
img {
  border: 0;
  background: none;
  max-width: 100%;
}
svg:not(:root) {
  overflow: hidden;
}
hr {
  -moz-box-sizing: content-box;
  box-sizing: content-box;
  -webkit-box-sizing: content-box;
  -moz-box-sizing: content-box;
  height: 0;
  border: 0;
  border-radius: 1px;
  -webkit-border-radius: 1px;
  border-bottom: 1px solid rgba(68,68,68,0.1);
}
button,
input {
  color: inherit;
  font: inherit;
  margin: 0;
}
button {
  overflow: visible;
  text-transform: none;
  -webkit-appearance: button;
  cursor: pointer;
}
@supports (backdrop-filter: blur(20px)) {
  .blur {
    background: rgba(255,255,255,0.9) !important;
    backdrop-filter: saturate(200%) blur(20px);
  }
}
@supports (backdrop-filter: blur(20px)) {
  .blur#comments {
    backdrop-filter: unset;
  }
}
#layoutHelper-comments {
  margin-bottom: 100px;
}
.shadow {
  box-shadow: 0 1px 2px 0px rgba(0,0,0,0.1);
  -webkit-box-shadow: 0 1px 2px 0px rgba(0,0,0,0.1);
}
.shadow.floatable {
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
}
.shadow.floatable:hover {
  box-shadow: 0 2px 4px 0px rgba(0,0,0,0.1), 0 4px 8px 0px rgba(0,0,0,0.1), 0 8px 16px 0px rgba(0,0,0,0.1);
  -webkit-box-shadow: 0 2px 4px 0px rgba(0,0,0,0.1), 0 4px 8px 0px rgba(0,0,0,0.1), 0 8px 16px 0px rgba(0,0,0,0.1);
}
#l_cover {
  min-height: 64px;
}
.cover-wrapper {
  top: 0;
  left: 0;
  max-width: 100%;
  height: 100vh;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  flex-wrap: nowrap;
  -webkit-flex-wrap: nowrap;
  -khtml-flex-wrap: nowrap;
  -moz-flex-wrap: nowrap;
  -o-flex-wrap: nowrap;
  -ms-flex-wrap: nowrap;
  -webkit-box-direction: normal;
  -moz-box-direction: normal;
  -webkit-box-orient: vertical;
  -moz-box-orient: vertical;
  -webkit-flex-direction: column;
  -ms-flex-direction: column;
  flex-direction: column;
  align-items: center;
  align-self: center;
  align-content: center;
  color: var(--color-site-inner);
  padding: 0 16px;
  user-select: none;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
  position: relative;
  overflow: hidden;
  margin-bottom: -100px;
}
.cover-wrapper .cover-bg {
  position: absolute;
  width: 100%;
  height: 100%;
  background-position: center;
  background-size: cover;
  -webkit-background-size: cover;
  -moz-background-size: cover;
}
.cover-wrapper .cover-bg.lazyload:not(.loaded) {
  opacity: 0;
  -webkit-opacity: 0;
  -moz-opacity: 0;
}
.cover-wrapper .cover-bg.lazyload.loaded {
  animation-delay: 0s;
  animation-duration: 0.5s;
  animation-fill-mode: forwards;
  animation-timing-function: ease-out;
  animation-name: fadeIn;
}
@-moz-keyframes fadeIn {
  0% {
    opacity: 0;
    -webkit-opacity: 0;
    -moz-opacity: 0;
    filter: blur(12px);
    transform: scale(1.02);
    -webkit-transform: scale(1.02);
    -khtml-transform: scale(1.02);
    -moz-transform: scale(1.02);
    -o-transform: scale(1.02);
    -ms-transform: scale(1.02);
  }
  100% {
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@-webkit-keyframes fadeIn {
  0% {
    opacity: 0;
    -webkit-opacity: 0;
    -moz-opacity: 0;
    filter: blur(12px);
    transform: scale(1.02);
    -webkit-transform: scale(1.02);
    -khtml-transform: scale(1.02);
    -moz-transform: scale(1.02);
    -o-transform: scale(1.02);
    -ms-transform: scale(1.02);
  }
  100% {
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@-o-keyframes fadeIn {
  0% {
    opacity: 0;
    -webkit-opacity: 0;
    -moz-opacity: 0;
    filter: blur(12px);
    transform: scale(1.02);
    -webkit-transform: scale(1.02);
    -khtml-transform: scale(1.02);
    -moz-transform: scale(1.02);
    -o-transform: scale(1.02);
    -ms-transform: scale(1.02);
  }
  100% {
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@keyframes fadeIn {
  0% {
    opacity: 0;
    -webkit-opacity: 0;
    -moz-opacity: 0;
    filter: blur(12px);
    transform: scale(1.02);
    -webkit-transform: scale(1.02);
    -khtml-transform: scale(1.02);
    -moz-transform: scale(1.02);
    -o-transform: scale(1.02);
    -ms-transform: scale(1.02);
  }
  100% {
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
.cover-wrapper .cover-body {
  z-index: 1;
  position: relative;
  width: 100%;
  height: 100%;
}
.cover-wrapper#full {
  height: calc(100vh + 100px);
  padding-bottom: 100px;
}
.cover-wrapper#half {
  max-height: 640px;
  min-height: 400px;
  height: calc(36vh - 64px + 200px);
}
.cover-wrapper #scroll-down {
  width: 100%;
  height: 64px;
  position: absolute;
  bottom: 100px;
  text-align: center;
  cursor: pointer;
}
.cover-wrapper #scroll-down .scroll-down-effects {
  color: #fff;
  font-size: 24px;
  line-height: 64px;
  position: absolute;
  width: 24px;
  left: calc(50% - 12px);
  text-shadow: 0 1px 2px rgba(0,0,0,0.1);
  animation: scroll-down-effect 1.5s infinite;
  -webkit-animation: scroll-down-effect 1.5s infinite;
  -khtml-animation: scroll-down-effect 1.5s infinite;
  -moz-animation: scroll-down-effect 1.5s infinite;
  -o-animation: scroll-down-effect 1.5s infinite;
  -ms-animation: scroll-down-effect 1.5s infinite;
}
@-moz-keyframes scroll-down-effect {
  0% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
  50% {
    top: -16px;
    opacity: 0.4;
    -webkit-opacity: 0.4;
    -moz-opacity: 0.4;
  }
  100% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@-webkit-keyframes scroll-down-effect {
  0% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
  50% {
    top: -16px;
    opacity: 0.4;
    -webkit-opacity: 0.4;
    -moz-opacity: 0.4;
  }
  100% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@-o-keyframes scroll-down-effect {
  0% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
  50% {
    top: -16px;
    opacity: 0.4;
    -webkit-opacity: 0.4;
    -moz-opacity: 0.4;
  }
  100% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@keyframes scroll-down-effect {
  0% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
  50% {
    top: -16px;
    opacity: 0.4;
    -webkit-opacity: 0.4;
    -moz-opacity: 0.4;
  }
  100% {
    top: 0;
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
.cover-wrapper .cover-body {
  margin-top: 64px;
  margin-bottom: 100px;
}
.cover-wrapper .cover-body,
.cover-wrapper .cover-body .top,
.cover-wrapper .cover-body .bottom {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  -webkit-box-direction: normal;
  -moz-box-direction: normal;
  -webkit-box-orient: vertical;
  -moz-box-orient: vertical;
  -webkit-flex-direction: column;
  -ms-flex-direction: column;
  flex-direction: column;
  align-items: center;
  justify-content: center;
  -webkit-justify-content: center;
  -khtml-justify-content: center;
  -moz-justify-content: center;
  -o-justify-content: center;
  -ms-justify-content: center;
  max-width: 100%;
}
.cover-wrapper .cover-body .bottom {
  margin-top: 32px;
}
.cover-wrapper .cover-body .title {
  font-family: "Varela Round", "PingFang SC", "Microsoft YaHei", Helvetica, Arial, Helvetica, monospace;
  font-size: 3.125rem;
  line-height: 1.2;
  text-shadow: 0 1px 2px rgba(0,0,0,0.1);
}
.cover-wrapper .cover-body .subtitle {
  font-size: 20px;
}
.cover-wrapper .cover-body .logo {
  max-height: 120px;
  max-width: calc(100% - 4 * 16px);
}
@media screen and (min-height: 1024px) {
  .cover-wrapper .cover-body .title {
    font-size: 3rem;
  }
  .cover-wrapper .cover-body .subtitle {
    font-size: 1.05rem;
  }
  .cover-wrapper .cover-body .logo {
    max-height: 150px;
  }
}
.cover-wrapper .cover-body .m_search {
  position: relative;
  max-width: calc(100% - 16px);
  width: 320px;
  vertical-align: middle;
}
.cover-wrapper .cover-body .m_search .form {
  position: relative;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
  width: 100%;
}
.cover-wrapper .cover-body .m_search .icon,
.cover-wrapper .cover-body .m_search .input {
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
}
.cover-wrapper .cover-body .m_search .icon {
  position: absolute;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
  line-height: 2.5rem;
  width: 32px;
  top: 0;
  left: 5px;
  color: rgba(68,68,68,0.75);
}
.cover-wrapper .cover-body .m_search .input {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
  height: 2.5rem;
  width: 100%;
  box-shadow: none;
  -webkit-box-shadow: none;
  box-sizing: border-box;
  -webkit-box-sizing: border-box;
  -moz-box-sizing: border-box;
  font-size: 0.875rem;
  -webkit-appearance: none;
  padding-left: 36px;
  border-radius: 1.4rem;
  -webkit-border-radius: 1.4rem;
  background: rgba(255,255,255,0.6);
  backdrop-filter: blur(10px);
  border: none;
  color: var(--color-text);
}
@media screen and (max-width: 500px) {
  .cover-wrapper .cover-body .m_search .input {
    padding-left: 36px;
  }
}
.cover-wrapper .cover-body .m_search .input:hover {
  background: rgba(255,255,255,0.8);
}
.cover-wrapper .cover-body .m_search .input:focus {
  background: #fff;
}
.cover-wrapper .list-h {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  -webkit-box-direction: normal;
  -moz-box-direction: normal;
  -webkit-box-orient: horizontal;
  -moz-box-orient: horizontal;
  -webkit-flex-direction: row;
  -ms-flex-direction: row;
  flex-direction: row;
  flex-wrap: wrap;
  -webkit-flex-wrap: wrap;
  -khtml-flex-wrap: wrap;
  -moz-flex-wrap: wrap;
  -o-flex-wrap: wrap;
  -ms-flex-wrap: wrap;
  align-items: stretch;
  border-radius: 4px;
  -webkit-border-radius: 4px;
  user-select: none;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
}
.cover-wrapper .list-h a {
  -webkit-box-flex: 1;
  -moz-box-flex: 1;
  -webkit-flex: 1 0;
  -ms-flex: 1 0;
  flex: 1 0;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  font-weight: 600;
}
.cover-wrapper .list-h a img {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
  border-radius: 2px;
  -webkit-border-radius: 2px;
  margin: 4px;
  min-width: 40px;
  max-width: 44px;
}
@media screen and (max-width: 768px) {
  .cover-wrapper .list-h a img {
    min-width: 36px;
    max-width: 40px;
  }
}
@media screen and (max-width: 500px) {
  .cover-wrapper .list-h a img {
    margin: 2px 4px;
    min-width: 32px;
    max-width: 36px;
  }
}
@media screen and (max-width: 375px) {
  .cover-wrapper .list-h a img {
    min-width: 28px;
    max-width: 32px;
  }
}
.cover-wrapper {
  max-width: 100%;
}
.cover-wrapper.search .bottom .menu {
  margin-top: 16px;
}
.cover-wrapper.search .bottom .menu .list-h a {
  white-space: nowrap;
  -webkit-box-direction: normal;
  -moz-box-direction: normal;
  -webkit-box-orient: horizontal;
  -moz-box-orient: horizontal;
  -webkit-flex-direction: row;
  -ms-flex-direction: row;
  flex-direction: row;
  align-items: baseline;
  padding: 2px;
  margin: 4px;
  color: var(--color-site-inner);
  opacity: 0.75;
  -webkit-opacity: 0.75;
  -moz-opacity: 0.75;
  text-shadow: 0 1px 2px rgba(0,0,0,0.05);
  border-bottom: 2px solid transparent;
}
.cover-wrapper.search .bottom .menu .list-h a i {
  margin-right: 4px;
}
.cover-wrapper.search .bottom .menu .list-h a p {
  font-size: 0.9375rem;
}
.cover-wrapper.search .bottom .menu .list-h a:hover,
.cover-wrapper.search .bottom .menu .list-h a.active,
.cover-wrapper.search .bottom .menu .list-h a:active {
  opacity: 1;
  -webkit-opacity: 1;
  -moz-opacity: 1;
  border-bottom: 2px solid var(--color-site-inner);
}
.cover-wrapper.dock .menu,
.cover-wrapper.featured .menu,
.cover-wrapper.focus .menu {
  border-radius: 6px;
  -webkit-border-radius: 6px;
}
.cover-wrapper.dock .menu .list-h a,
.cover-wrapper.featured .menu .list-h a,
.cover-wrapper.focus .menu .list-h a {
  -webkit-box-direction: normal;
  -moz-box-direction: normal;
  -webkit-box-orient: vertical;
  -moz-box-orient: vertical;
  -webkit-flex-direction: column;
  -ms-flex-direction: column;
  flex-direction: column;
  align-items: center;
  padding: 12px;
  line-height: 24px;
  border-radius: 4px;
  -webkit-border-radius: 4px;
  border-bottom: none;
  text-align: center;
  align-content: flex-end;
  color: rgba(68,68,68,0.7);
  font-size: 1.5rem;
}
@media screen and (max-width: 500px) {
  .cover-wrapper.dock .menu .list-h a,
  .cover-wrapper.featured .menu .list-h a,
  .cover-wrapper.focus .menu .list-h a {
    padding: 12px 8px;
  }
}
.cover-wrapper.dock .menu .list-h a i,
.cover-wrapper.featured .menu .list-h a i,
.cover-wrapper.focus .menu .list-h a i {
  margin: 8px;
}
.cover-wrapper.dock .menu .list-h a p,
.cover-wrapper.featured .menu .list-h a p,
.cover-wrapper.focus .menu .list-h a p {
  font-size: 0.875rem;
}
.cover-wrapper.dock .menu .list-h a.active,
.cover-wrapper.featured .menu .list-h a.active,
.cover-wrapper.focus .menu .list-h a.active {
  background: var(--color-card);
  backdrop-filter: none;
}
.cover-wrapper.dock .menu .list-h a.active i,
.cover-wrapper.featured .menu .list-h a.active i,
.cover-wrapper.focus .menu .list-h a.active i,
.cover-wrapper.dock .menu .list-h a.active i+p,
.cover-wrapper.featured .menu .list-h a.active i+p,
.cover-wrapper.focus .menu .list-h a.active i+p {
  color: #03a9f4;
}
.cover-wrapper.dock .menu .list-h a.active img+p,
.cover-wrapper.featured .menu .list-h a.active img+p,
.cover-wrapper.focus .menu .list-h a.active img+p {
  color: var(--color-text);
}
.cover-wrapper.dock .menu .list-h a:hover,
.cover-wrapper.featured .menu .list-h a:hover,
.cover-wrapper.focus .menu .list-h a:hover {
  background: var(--color-card);
}
.cover-wrapper.dock .top {
  margin-bottom: 48px;
}
.cover-wrapper.dock .menu {
  background: rgba(255,255,255,0.5);
  position: absolute;
  bottom: 0;
  max-width: 100%;
}
.cover-wrapper.dock .menu .list-h {
  flex-wrap: nowrap;
  -webkit-flex-wrap: nowrap;
  -khtml-flex-wrap: nowrap;
  -moz-flex-wrap: nowrap;
  -o-flex-wrap: nowrap;
  -ms-flex-wrap: nowrap;
  margin: 4px;
}
.cover-wrapper.dock .menu .list-h a+a {
  margin-left: 4px;
}
@media screen and (max-width: 500px) {
  .cover-wrapper.dock .menu .list-h {
    overflow-x: scroll;
  }
  .cover-wrapper.dock .menu .list-h::-webkit-scrollbar {
    height: 0;
    width: 0;
  }
  .cover-wrapper.dock .menu .list-h::-webkit-scrollbar-track-piece {
    background: transparent;
  }
  .cover-wrapper.dock .menu .list-h::-webkit-scrollbar-thumb {
    background: #03a9f4;
    cursor: pointer;
    border-radius: 0;
    -webkit-border-radius: 0;
  }
  .cover-wrapper.dock .menu .list-h::-webkit-scrollbar-thumb:hover {
    background: #ff5722;
  }
}
@supports (backdrop-filter: blur(20px)) {
  .cover-wrapper.dock .menu {
    background: rgba(255,255,255,0.5);
    backdrop-filter: saturate(200%) blur(20px);
  }
}
@media (prefers-color-scheme: dark) {
  :root {
    --color-mode: 'dark';
  }
  :root:not([color-scheme]) {
    --color-site-body: #121212;
    --color-read-bkg: #1f1f1f;
    --color-read-post: #262626;
    --color-site-bg: #1f1f1f;
    --color-site-inner: rgba(238,238,238,0.871);
    --color-site-footer: rgba(170,170,170,0.871);
    --color-card: #262626;
    --color-text: #eee;
    --color-block: #434343;
    --color-codeblock: #1f1f1f;
    --color-inlinecode: #d56d28;
    --color-h1: rgba(255,255,255,0.871);
    --color-h2: rgba(255,255,255,0.871);
    --color-h3: rgba(255,255,255,0.6);
    --color-h4: rgba(255,255,255,0.6);
    --color-h5: rgba(255,255,255,0.6);
    --color-h6: rgba(255,255,255,0.6);
    --color-p: rgba(217,217,217,0.871);
    --color-list: rgba(217,217,217,0.871);
    --color-list-hl: #35baf6;
    --color-meta: rgba(191,191,191,0.871);
    --color-link: rgba(191,191,191,0.871);
  }
  :root:not([color-scheme]) img {
    filter: brightness(70%) !important;
  }
  :root:not([color-scheme]) .blur {
    background: rgba(31,31,31,0.9) !important;
  }
  :root:not([color-scheme]) .white-box.blur {
    background: rgba(38,38,38,0.9) !important;
  }
  :root:not([color-scheme]) .nav-main .u-search-input {
    background: var(--color-card) !important;
  }
  :root:not([color-scheme]) #l_main .article .prev-next>a {
    background: var(--color-block) !important;
  }
  :root:not([color-scheme]) #l_main .article .prev-next>a:hover {
    background: var(--color-site-bg) !important;
  }
  :root:not([color-scheme]) .article blockquote {
    background: var(--color-block) !important;
  }
  :root:not([color-scheme]) .article-title a {
    color: var(--color-h1) !important;
  }
  :root:not([color-scheme]) details>summary {
    color: var(--color-p) !important;
    background: var(--color-site-bg) !important;
  }
  :root:not([color-scheme]) details {
    border: 1px solid var(--color-site-bg) !important;
    background: var(--color-site-bg) !important;
  }
  :root:not([color-scheme]) #u-search .modal,
  :root:not([color-scheme]) #u-search .modal-header,
  :root:not([color-scheme]) #u-search .modal-body {
    background: var(--color-card) !important;
  }
  :root:not([color-scheme]) #u-search .modal-body .modal-results .result:hover {
    background: var(--color-block) !important;
  }
  :root:not([color-scheme]) .u-search-input:hover {
    background: var(--color-block) !important;
  }
  :root:not([color-scheme]) .u-search-input:focus {
    background: var(--color-site-body) !important;
  }
}
[color-scheme='dark'] {
  --color-site-body: #121212;
  --color-read-bkg: #1f1f1f;
  --color-read-post: #262626;
  --color-site-bg: #1f1f1f;
  --color-site-inner: rgba(238,238,238,0.871);
  --color-site-footer: rgba(170,170,170,0.871);
  --color-card: #262626;
  --color-text: #eee;
  --color-block: #434343;
  --color-codeblock: #1f1f1f;
  --color-inlinecode: #d56d28;
  --color-h1: rgba(255,255,255,0.871);
  --color-h2: rgba(255,255,255,0.871);
  --color-h3: rgba(255,255,255,0.6);
  --color-h4: rgba(255,255,255,0.6);
  --color-h5: rgba(255,255,255,0.6);
  --color-h6: rgba(255,255,255,0.6);
  --color-p: rgba(217,217,217,0.871);
  --color-list: rgba(217,217,217,0.871);
  --color-list-hl: #35baf6;
  --color-meta: rgba(191,191,191,0.871);
  --color-link: rgba(191,191,191,0.871);
}
[color-scheme='dark'] img {
  filter: brightness(70%) !important;
}
[color-scheme='dark'] .blur {
  background: rgba(31,31,31,0.9) !important;
}
[color-scheme='dark'] .white-box.blur {
  background: rgba(38,38,38,0.9) !important;
}
[color-scheme='dark'] .nav-main .u-search-input {
  background: var(--color-card) !important;
}
[color-scheme='dark'] #l_main .article .prev-next>a {
  background: var(--color-block) !important;
}
[color-scheme='dark'] #l_main .article .prev-next>a:hover {
  background: var(--color-site-bg) !important;
}
[color-scheme='dark'] .article blockquote {
  background: var(--color-block) !important;
}
[color-scheme='dark'] .article-title a {
  color: var(--color-h1) !important;
}
[color-scheme='dark'] details>summary {
  color: var(--color-p) !important;
  background: var(--color-site-bg) !important;
}
[color-scheme='dark'] details {
  border: 1px solid var(--color-site-bg) !important;
  background: var(--color-site-bg) !important;
}
[color-scheme='dark'] #u-search .modal,
[color-scheme='dark'] #u-search .modal-header,
[color-scheme='dark'] #u-search .modal-body {
  background: var(--color-card) !important;
}
[color-scheme='dark'] #u-search .modal-body .modal-results .result:hover {
  background: var(--color-block) !important;
}
[color-scheme='dark'] .u-search-input:hover {
  background: var(--color-block) !important;
}
[color-scheme='dark'] .u-search-input:focus {
  background: var(--color-site-body) !important;
}
@media screen and (max-width: 500px) {
  [color-scheme='dark'] .l_header .m_search {
    background: var(--color-site-bg) !important;
  }
}
@font-face {
  font-family: 'UbuntuMono';
  src: url("https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/fonts/UbuntuMono/UbuntuMono-Regular.ttf");
  font-weight: 'normal';
  font-style: 'normal';
  font-display: swap;
}
@font-face {
  font-family: 'Varela Round';
  src: url("https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/fonts/VarelaRound/VarelaRound-Regular.ttf");
  font-weight: 'normal';
  font-style: 'normal';
  font-display: swap;
}
.l_header {
  position: fixed;
  z-index: 1000;
  top: 0;
  width: 100%;
  height: 64px;
  background: var(--color-card);
  box-shadow: 0 1px 2px 0px rgba(0,0,0,0.1);
  -webkit-box-shadow: 0 1px 2px 0px rgba(0,0,0,0.1);
}
.l_header.auto {
  transition: opacity 0.4s ease;
  -webkit-transition: opacity 0.4s ease;
  -khtml-transition: opacity 0.4s ease;
  -moz-transition: opacity 0.4s ease;
  -o-transition: opacity 0.4s ease;
  -ms-transition: opacity 0.4s ease;
  visibility: hidden;
}
.l_header.auto.show {
  opacity: 1 !important;
  -webkit-opacity: 1 !important;
  -moz-opacity: 1 !important;
  visibility: visible;
}
.l_header .container {
  margin-left: 16px;
  margin-right: 16px;
}
.l_header #wrapper {
  height: 100%;
  user-select: none;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
}
.l_header #wrapper .nav-main,
.l_header #wrapper .nav-sub {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  flex-wrap: nowrap;
  -webkit-flex-wrap: nowrap;
  -khtml-flex-wrap: nowrap;
  -moz-flex-wrap: nowrap;
  -o-flex-wrap: nowrap;
  -ms-flex-wrap: nowrap;
  justify-content: space-between;
  -webkit-justify-content: space-between;
  -khtml-justify-content: space-between;
  -moz-justify-content: space-between;
  -o-justify-content: space-between;
  -ms-justify-content: space-between;
  align-items: center;
}
.l_header #wrapper .nav-main {
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
}
.l_header #wrapper.sub .nav-main {
  transform: translateY(-64px);
  -webkit-transform: translateY(-64px);
  -khtml-transform: translateY(-64px);
  -moz-transform: translateY(-64px);
  -o-transform: translateY(-64px);
  -ms-transform: translateY(-64px);
}
.l_header #wrapper .nav-sub {
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
  opacity: 0;
  -webkit-opacity: 0;
  -moz-opacity: 0;
  height: 64px;
  width: calc(100% - 2 * 16px);
  position: absolute;
}
.l_header #wrapper .nav-sub ::-webkit-scrollbar {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: none;
}
@media screen and (min-width: 2048px) {
  .l_header #wrapper .nav-sub {
    max-width: 55vw;
    margin: auto;
  }
}
.l_header #wrapper.sub .nav-sub {
  opacity: 1;
  -webkit-opacity: 1;
  -moz-opacity: 1;
}
.l_header #wrapper .title {
  position: relative;
  color: var(--color-text);
  padding-left: 24px;
  max-height: 64px;
}
.l_header #wrapper .nav-main .title {
  white-space: nowrap;
  overflow: hidden;
  text-overflow: ellipsis;
  flex-shrink: 0;
  line-height: 64px;
  padding: 0 24px;
  font-size: 1.25rem;
  font-family: "Varela Round", "PingFang SC", "Microsoft YaHei", Helvetica, Arial, Helvetica, monospace;
}
.l_header #wrapper .nav-main .title img {
  height: 64px;
}
.l_header .nav-sub {
  max-width: 1080px;
  margin: auto;
}
.l_header .nav-sub .title {
  font-weight: bold;
  font-family: UbuntuMono, "Varela Round", "PingFang SC", "Microsoft YaHei", Helvetica, Arial, Menlo, Monaco, monospace, sans-serif;
  line-height: 1.2;
  max-height: 64px;
  white-space: normal;
  flex-shrink: 1;
}
.l_header .switcher {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: none;
  line-height: 64px;
  align-items: center;
}
.l_header .switcher .s-toc {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: none;
}
@media screen and (max-width: 768px) {
  .l_header .switcher .s-toc {
    display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
    display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
    display: -ms-flexbox /* TWEENER - IE 10 */;
    display: -webkit-flex /* NEW - Chrome */;
    display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
    display: flex;
  }
}
.l_header .switcher >li {
  height: 48px;
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
  margin: 2px;
}
@media screen and (max-width: 500px) {
  .l_header .switcher >li {
    margin: 0 1px;
    height: 48px;
  }
}
.l_header .switcher >li >a {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  justify-content: center;
  -webkit-justify-content: center;
  -khtml-justify-content: center;
  -moz-justify-content: center;
  -o-justify-content: center;
  -ms-justify-content: center;
  align-items: center;
  width: 48px;
  height: 48px;
  padding: 0.85em 1.1em;
  border-radius: 100px;
  -webkit-border-radius: 100px;
  border: none;
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
  color: #03a9f4;
}
.l_header .switcher >li >a:hover {
  border: none;
}
.l_header .switcher >li >a.active,
.l_header .switcher >li >a:active {
  border: none;
  background: var(--color-site-bg);
}
@media screen and (max-width: 500px) {
  .l_header .switcher >li >a {
    width: 36px;
    height: 48px;
  }
}
.l_header .nav-sub .switcher {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
}
.l_header .m_search {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  height: 64px;
  width: 240px;
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
}
@media screen and (max-width: 1024px) {
  .l_header .m_search {
    width: 44px;
    min-width: 44px;
  }
  .l_header .m_search input::placeholder {
    opacity: 0;
    -webkit-opacity: 0;
    -moz-opacity: 0;
  }
  .l_header .m_search:hover {
    width: 240px;
  }
  .l_header .m_search:hover input::placeholder {
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@media screen and (min-width: 500px) {
  .l_header .m_search:hover .input {
    width: 100%;
  }
  .l_header .m_search:hover .input::placeholder {
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
@media screen and (max-width: 500px) {
  .l_header .m_search {
    min-width: 0;
  }
  .l_header .m_search input::placeholder {
    opacity: 1;
    -webkit-opacity: 1;
    -moz-opacity: 1;
  }
}
.l_header .m_search .form {
  position: relative;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  width: 100%;
  align-items: center;
}
.l_header .m_search .icon {
  position: absolute;
  width: 36px;
  left: 5px;
  color: var(--color-meta);
}
@media screen and (max-width: 500px) {
  .l_header .m_search .icon {
    display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
    display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
    display: none;
  }
}
.l_header .m_search .input {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
  padding-top: 8px;
  padding-bottom: 8px;
  line-height: 1.3;
  width: 100%;
  color: var(--color-text);
  background: #fafafa;
  box-shadow: none;
  -webkit-box-shadow: none;
  box-sizing: border-box;
  -webkit-box-sizing: border-box;
  -moz-box-sizing: border-box;
  padding-left: 40px;
  font-size: 0.875rem;
  border-radius: 8px;
  -webkit-border-radius: 8px;
  border: none;
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
}
@media screen and (min-width: 500px) {
  .l_header .m_search .input:focus {
    box-shadow: 0 4px 8px 0px rgba(0,0,0,0.1);
    -webkit-box-shadow: 0 4px 8px 0px rgba(0,0,0,0.1);
  }
}
@media screen and (max-width: 500px) {
  .l_header .m_search .input {
    background: var(--color-block);
    padding-left: 8px;
    border: none;
  }
  .l_header .m_search .input:hover,
  .l_header .m_search .input:focus {
    border: none;
  }
}
@media (max-width: 500px) {
  .l_header .m_search {
    left: 0;
    width: 0;
    overflow: hidden;
    position: absolute;
    background: #fff;
    transition: all 0.28s ease;
    -webkit-transition: all 0.28s ease;
    -khtml-transition: all 0.28s ease;
    -moz-transition: all 0.28s ease;
    -o-transition: all 0.28s ease;
    -ms-transition: all 0.28s ease;
  }
  .l_header .m_search .input {
    border-radius: 32px;
    -webkit-border-radius: 32px;
    margin-left: 16px;
    padding-left: 16px;
  }
  .l_header.z_search-open .m_search {
    width: 100%;
  }
  .l_header.z_search-open .m_search .input {
    width: calc(100% - 120px);
  }
}
ul.m-pc >li>a {
  color: inherit;
  border-bottom: 2px solid transparent;
}
ul.m-pc >li>a:active,
ul.m-pc >li>a.active {
  border-bottom: 2px solid #03a9f4;
}
ul.m-pc li:hover >ul.list-v,
ul.list-v li:hover >ul.list-v {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
}
ul.nav-list-h {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: -ms-flexbox /* TWEENER - IE 10 */;
  display: -webkit-flex /* NEW - Chrome */;
  display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
  display: flex;
  align-items: stretch;
}
ul.nav-list-h>li {
  position: relative;
  justify-content: center;
  -webkit-justify-content: center;
  -khtml-justify-content: center;
  -moz-justify-content: center;
  -o-justify-content: center;
  -ms-justify-content: center;
  height: 100%;
  line-height: 2.4;
  border-radius: 4px;
  -webkit-border-radius: 4px;
}
ul.nav-list-h>li >a {
  -webkit-font-smoothing: antialiased;
  -moz-osx-font-smoothing: grayscale;
  font-weight: 600;
}
ul.list-v {
  z-index: 1;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: none;
  position: absolute;
  background: var(--color-card);
  box-shadow: 0 2px 4px 0px rgba(0,0,0,0.08), 0 4px 8px 0px rgba(0,0,0,0.08), 0 8px 16px 0px rgba(0,0,0,0.08);
  -webkit-box-shadow: 0 2px 4px 0px rgba(0,0,0,0.08), 0 4px 8px 0px rgba(0,0,0,0.08), 0 8px 16px 0px rgba(0,0,0,0.08);
  margin-top: -6px;
  border-radius: 4px;
  -webkit-border-radius: 4px;
  padding: 8px 0;
}
ul.list-v.show {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
}
ul.list-v hr {
  margin-top: 8px;
  margin-bottom: 8px;
}
ul.list-v >li {
  white-space: nowrap;
  word-break: keep-all;
}
ul.list-v >li.header {
  font-size: 0.78125rem;
  font-weight: bold;
  line-height: 2em;
  color: var(--color-meta);
  margin: 8px 16px 4px;
}
ul.list-v >li.header i {
  margin-right: 8px;
}
ul.list-v >li ul {
  margin-left: 0;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: none;
  margin-top: -40px;
}
ul.list-v .aplayer-container {
  min-height: 64px;
  padding: 6px 16px;
}
ul.list-v >li>a {
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
  color: var(--color-list);
  font-size: 0.875rem;
  font-weight: bold;
  line-height: 36px;
  padding: 0 20px 0 16px;
  text-overflow: ellipsis;
  margin: 0 4px;
  border-radius: 4px;
  -webkit-border-radius: 4px;
}
@media screen and (max-width: 1024px) {
  ul.list-v >li>a {
    line-height: 40px;
  }
}
ul.list-v >li>a >i {
  margin-right: 8px;
}
ul.list-v >li>a:active,
ul.list-v >li>a.active {
  color: var(--color-list-hl);
}
ul.list-v >li>a:hover {
  color: var(--color-list-hl);
  background: var(--color-site-bg);
}
.l_header .menu >ul>li>a {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: block;
  padding: 0 8px;
}
.l_header .menu >ul>li>a >i {
  margin-right: 4px;
}
.l_header ul.nav-list-h>li {
  color: var(--color-list);
  line-height: 64px;
}
.l_header ul.nav-list-h>li >a {
  max-height: 64px;
  overflow: hidden;
  color: inherit;
}
.l_header ul.nav-list-h>li >a:active,
.l_header ul.nav-list-h>li >a.active {
  color: #03a9f4;
}
.l_header ul.nav-list-h>li:hover>a {
  color: var(--color-list-hl);
}
.l_header ul.nav-list-h>li i.music {
  animation: rotate-effect 1.5s linear infinite;
  -webkit-animation: rotate-effect 1.5s linear infinite;
  -khtml-animation: rotate-effect 1.5s linear infinite;
  -moz-animation: rotate-effect 1.5s linear infinite;
  -o-animation: rotate-effect 1.5s linear infinite;
  -ms-animation: rotate-effect 1.5s linear infinite;
}
@-moz-keyframes rotate-effect {
  0% {
    transform: rotate(0);
    -webkit-transform: rotate(0);
    -khtml-transform: rotate(0);
    -moz-transform: rotate(0);
    -o-transform: rotate(0);
    -ms-transform: rotate(0);
  }
  25% {
    transform: rotate(90deg);
    -webkit-transform: rotate(90deg);
    -khtml-transform: rotate(90deg);
    -moz-transform: rotate(90deg);
    -o-transform: rotate(90deg);
    -ms-transform: rotate(90deg);
  }
  50% {
    transform: rotate(180deg);
    -webkit-transform: rotate(180deg);
    -khtml-transform: rotate(180deg);
    -moz-transform: rotate(180deg);
    -o-transform: rotate(180deg);
    -ms-transform: rotate(180deg);
  }
  75% {
    transform: rotate(270deg);
    -webkit-transform: rotate(270deg);
    -khtml-transform: rotate(270deg);
    -moz-transform: rotate(270deg);
    -o-transform: rotate(270deg);
    -ms-transform: rotate(270deg);
  }
  100% {
    transform: rotate(360deg);
    -webkit-transform: rotate(360deg);
    -khtml-transform: rotate(360deg);
    -moz-transform: rotate(360deg);
    -o-transform: rotate(360deg);
    -ms-transform: rotate(360deg);
  }
}
@-webkit-keyframes rotate-effect {
  0% {
    transform: rotate(0);
    -webkit-transform: rotate(0);
    -khtml-transform: rotate(0);
    -moz-transform: rotate(0);
    -o-transform: rotate(0);
    -ms-transform: rotate(0);
  }
  25% {
    transform: rotate(90deg);
    -webkit-transform: rotate(90deg);
    -khtml-transform: rotate(90deg);
    -moz-transform: rotate(90deg);
    -o-transform: rotate(90deg);
    -ms-transform: rotate(90deg);
  }
  50% {
    transform: rotate(180deg);
    -webkit-transform: rotate(180deg);
    -khtml-transform: rotate(180deg);
    -moz-transform: rotate(180deg);
    -o-transform: rotate(180deg);
    -ms-transform: rotate(180deg);
  }
  75% {
    transform: rotate(270deg);
    -webkit-transform: rotate(270deg);
    -khtml-transform: rotate(270deg);
    -moz-transform: rotate(270deg);
    -o-transform: rotate(270deg);
    -ms-transform: rotate(270deg);
  }
  100% {
    transform: rotate(360deg);
    -webkit-transform: rotate(360deg);
    -khtml-transform: rotate(360deg);
    -moz-transform: rotate(360deg);
    -o-transform: rotate(360deg);
    -ms-transform: rotate(360deg);
  }
}
@-o-keyframes rotate-effect {
  0% {
    transform: rotate(0);
    -webkit-transform: rotate(0);
    -khtml-transform: rotate(0);
    -moz-transform: rotate(0);
    -o-transform: rotate(0);
    -ms-transform: rotate(0);
  }
  25% {
    transform: rotate(90deg);
    -webkit-transform: rotate(90deg);
    -khtml-transform: rotate(90deg);
    -moz-transform: rotate(90deg);
    -o-transform: rotate(90deg);
    -ms-transform: rotate(90deg);
  }
  50% {
    transform: rotate(180deg);
    -webkit-transform: rotate(180deg);
    -khtml-transform: rotate(180deg);
    -moz-transform: rotate(180deg);
    -o-transform: rotate(180deg);
    -ms-transform: rotate(180deg);
  }
  75% {
    transform: rotate(270deg);
    -webkit-transform: rotate(270deg);
    -khtml-transform: rotate(270deg);
    -moz-transform: rotate(270deg);
    -o-transform: rotate(270deg);
    -ms-transform: rotate(270deg);
  }
  100% {
    transform: rotate(360deg);
    -webkit-transform: rotate(360deg);
    -khtml-transform: rotate(360deg);
    -moz-transform: rotate(360deg);
    -o-transform: rotate(360deg);
    -ms-transform: rotate(360deg);
  }
}
@keyframes rotate-effect {
  0% {
    transform: rotate(0);
    -webkit-transform: rotate(0);
    -khtml-transform: rotate(0);
    -moz-transform: rotate(0);
    -o-transform: rotate(0);
    -ms-transform: rotate(0);
  }
  25% {
    transform: rotate(90deg);
    -webkit-transform: rotate(90deg);
    -khtml-transform: rotate(90deg);
    -moz-transform: rotate(90deg);
    -o-transform: rotate(90deg);
    -ms-transform: rotate(90deg);
  }
  50% {
    transform: rotate(180deg);
    -webkit-transform: rotate(180deg);
    -khtml-transform: rotate(180deg);
    -moz-transform: rotate(180deg);
    -o-transform: rotate(180deg);
    -ms-transform: rotate(180deg);
  }
  75% {
    transform: rotate(270deg);
    -webkit-transform: rotate(270deg);
    -khtml-transform: rotate(270deg);
    -moz-transform: rotate(270deg);
    -o-transform: rotate(270deg);
    -ms-transform: rotate(270deg);
  }
  100% {
    transform: rotate(360deg);
    -webkit-transform: rotate(360deg);
    -khtml-transform: rotate(360deg);
    -moz-transform: rotate(360deg);
    -o-transform: rotate(360deg);
    -ms-transform: rotate(360deg);
  }
}
.menu-phone li ul.list-v {
  right: calc(100% - 0.5 * 16px);
}
.menu-phone li ul.list-v ul {
  right: calc(100% - 0.5 * 16px);
}
#wrapper {
  max-width: 1080px;
  margin: auto;
}
@media screen and (min-width: 2048px) {
  #wrapper {
    max-width: 55vw;
  }
}
#wrapper .menu {
  -webkit-box-flex: 1;
  -moz-box-flex: 1;
  -webkit-flex: 1 1;
  -ms-flex: 1 1;
  flex: 1 1;
  margin: 0 16px 0 0;
}
#wrapper .menu .list-v ul {
  left: calc(100% - 0.5 * 16px);
}
.menu-phone {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: none;
  margin-top: 16px;
  right: 8px;
  transition: all 0.28s ease;
  -webkit-transition: all 0.28s ease;
  -khtml-transition: all 0.28s ease;
  -moz-transition: all 0.28s ease;
  -o-transition: all 0.28s ease;
  -ms-transition: all 0.28s ease;
}
.menu-phone ul {
  right: calc(100% - 0.5 * 16px);
}
@media screen and (max-width: 500px) {
  .menu-phone {
    display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
    display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
    display: block;
  }
}
.l_header {
  max-width: 65vw;
  left: calc((100% - 65vw) * 0.5);
  border-bottom-left-radius: 8px;
  border-bottom-right-radius: 8px;
}
@media screen and (max-width: 2048px) {
  .l_header {
    max-width: 1112px;
    left: calc((100% - 1112px) * 0.5);
  }
}
@media screen and (max-width: 1112px) {
  .l_header {
    left: 0;
    border-radius: 0;
    -webkit-border-radius: 0;
    max-width: 100%;
  }
}
@media screen and (max-width: 500px) {
  .l_header .container {
    margin-left: 0;
    margin-right: 0;
  }
  .l_header #wrapper .nav-main .title {
    padding-left: 16px;
    padding-right: 16px;
  }
  .l_header #wrapper .nav-sub {
    width: 100%;
  }
  .l_header #wrapper .nav-sub .title {
    overflow-y: scroll;
    margin-top: 2px;
    padding: 8px 16px;
  }
  .l_header #wrapper .switcher {
    display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
    display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
    display: -ms-flexbox /* TWEENER - IE 10 */;
    display: -webkit-flex /* NEW - Chrome */;
    display: flex /* NEW, Spec - Opera 12.1, Firefox 20+ */;
    display: flex;
    margin-right: 8px;
  }
  .l_header .menu {
    display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
    display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
    display: none;
  }
}
@media screen and (max-width: 500px) {
  .list-v li {
    max-width: 270px;
  }
}
#u-search {
  display: -webkit-box /* OLD - iOS 6-, Safari 3.1-6 */;
  display: -moz-box /* OLD - Firefox 19- (buggy but mostly works) */;
  display: none;
  position: fixed;
  top: 0;
  left: 0;
  width: 100%;
  height: 100%;
  padding: 60px 20px;
  z-index: 1001;
}
@media screen and (max-width: 680px) {
  #u-search {
    padding: 0px;
  }
}
@media screen and (prefers-color-scheme: dark) and (max-width: 500px) {
  .l_header .m_search {
    background: var(--color-site-bg) !important;
  }
}

  </style>
  <link rel="stylesheet" href="/css/style.c5d207c8.css" media="print" onload="this.media='all';this.onload=null">
  <noscript><link rel="stylesheet" href="/css/style.c5d207c8.css"></noscript>
    <script>
      let userColorScheme=localStorage.getItem("color-scheme")
      if(userColorScheme){
        document.documentElement.setAttribute("color-scheme", userColorScheme);
      }
    </script>
  
<script>
if (/*@cc_on!@*/false || (!!window.MSInputMethodContext && !!document.documentMode))
    document.write(
	'<style>'+
		'html{'+
			'overflow-x: hidden !important;'+
			'overflow-y: hidden !important;'+
		'}'+
		'.kill-ie{'+
			'text-align:center;'+
			'height: 100%;'+
			'margin-top: 15%;'+
			'margin-bottom: 5500%;'+
		'}'+
    '.kill-t{'+
      'font-size: 2rem;'+
    '}'+
    '.kill-c{'+
      'font-size: 1.2rem;'+
    '}'+
		'#l_header,#l_body{'+
			'display: none;'+
		'}'+
	'</style>'+
    '<div class="kill-ie">'+
        `<span class="kill-t"><b>抱歉，您的浏览器无法访问本站</b></span><br/>`+
        `<span class="kill-c">微软已经于2016年终止了对 Internet Explorer (IE) 10 及更早版本的支持，<br/>继续使用存在极大的安全隐患，请使用当代主流的浏览器进行访问。</span><br/>`+
        `<a target="_blank" rel="noopener" href="https://www.microsoft.com/zh-cn/WindowsForBusiness/End-of-IE-support"><strong>了解详情 ></strong></a>`+
    '</div>');
</script>


<noscript>
	<style>
		html{
			overflow-x: hidden !important;
			overflow-y: hidden !important;
		}
		.kill-noscript{
			text-align:center;
			height: 100%;
			margin-top: 15%;
			margin-bottom: 5500%;
		}
    .kill-t{
      font-size: 2rem;
    }
    .kill-c{
      font-size: 1.2rem;
    }
		#l_header,#l_body{
			display: none;
		}
	</style>
    <div class="kill-noscript">
        <span class="kill-t"><b>抱歉，您的浏览器无法访问本站</b></span><br/>
        <span class="kill-c">本页面需要浏览器支持（启用）JavaScript</span><br/>
        <a target="_blank" rel="noopener" href="https://www.baidu.com/s?wd=启用JavaScript"><strong>了解详情 ></strong></a>
    </div>
</noscript>


  <script>
  /************这个文件存放不需要重载的全局变量和全局函数*********/
  window.volantis = {}; // volantis 全局变量
  volantis.debug = false; // 开启调试模式
  volantis.dom = {}; // 页面Dom see: /source/js/app.js etc.

  volantis.GLOBAL_CONFIG ={
    debug: false,
    cdn: {"js":{"app":"/js/app.e546c981.js","parallax":"/js/plugins/parallax.8bf0ab10.js","rightMenu":"/js/plugins/rightMenu.d9437285.js","rightMenus":"/js/plugins/rightMenus.cd02aa19.js","sites":"/js/plugins/tags/sites.76bf19b8.js","friends":"/js/plugins/tags/friends.f372da57.js","contributors":"/js/plugins/tags/contributors.aec80453.js","search":"/js/search/hexo.0e52f222.js"},"css":{"style":"/css/style.c5d207c8.css"}},
    default: {"avatar":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/placeholder/avatar/round/3442075.svg","link":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/placeholder/link/8f277b4ee0ecd.svg","cover":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/placeholder/cover/76b86c0226ffd.svg","image":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/placeholder/image/2659360.svg"},
    lastupdate: new Date(1654131979058),
    sidebar: {
      for_page: ["repos","blogger","category","tagcloud","webinfo"],
      for_post: ["toc"],
      webinfo: {
        lastupd: {
          enable: true,
          friendlyShow: true
        },
        runtime: {
          data: "2017/01/01",
          unit: "天"
        }
      }
    },
    plugins: {
      message: {"enable":true,"css":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/izitoast/dist/css/iziToast.min.css","js":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/izitoast/dist/js/iziToast.min.js","icon":{"default":"fa-solid fa-info-circle light-blue","quection":"fa-solid fa-question-circle light-blue"},"time":{"default":5000,"quection":20000},"position":"topRight","transitionIn":"bounceInLeft","transitionOut":"fadeOutRight","titleColor":"var(--color-text)","messageColor":"var(--color-text)","backgroundColor":"var(--color-card)","zindex":2147483647,"copyright":{"enable":true,"title":"知识共享许可协议","message":"请遵守 CC BY-NC-SA 4.0 协议。","icon":"far fa-copyright light-blue"},"aplayer":{"enable":true,"play":"fa-solid fa-play","pause":"fa-solid fa-pause"},"rightmenu":{"enable":true,"notice":true}},
      fancybox: {"css":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/@fancyapps/ui/dist/fancybox.css","js":"https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/@fancyapps/ui/dist/fancybox.umd.js"},
      aplayer: {
        id: 3175833810,
        enable:null
      },
      rightmenu: {
        faicon: "fa",
        layout: ["home","hr","help","examples","contributors","hr","source_docs","source_theme","hr","print","darkmode","reading","music"],
        music_alwaysShow: true,
        customPicUrl: {"enable":false,"old":null,"new":null}
      },
      rightmenus: {"enable":false,"order":["plugins.navigation","hr","plugins.inputBox","plugins.seletctText","plugins.elementCheck","plugins.elementImage","menus.link","hr","menus.darkMode","plugins.articlePage","music"],"options":{"iconPrefix":"fa-solid","articleShowLink":false,"musicAlwaysShow":false,"corsAnywhere":null},"plugins":{"navigation":[{"id":"left","name":"转到上一页","icon":"fa-solid fa-arrow-left","event":"history.back()","group":"navigation"},{"id":"right","name":"转到下一页","icon":"fa-solid fa-arrow-right","event":"history.forward()","group":"navigation"},{"id":"redo","name":"刷新当前页面","icon":"fa-solid fa-redo","event":"window.location.reload()","group":"navigation"},{"id":"up","name":"回到顶部","icon":"fa-solid fa-arrow-up","event":"VolantisApp.scrolltoElement(volantis.dom.bodyAnchor)","group":"navigation"}],"inputBox":[{"id":"copyPaste","name":"粘贴文本","icon":"fa-solid fa-paste","event":"copyPaste","group":"inputBox"},{"id":"copyAll","name":"全选文本","icon":"fa-solid fa-object-ungroup","event":"copyAll","group":"inputBox"},{"id":"copyCut","name":"剪切文本","icon":"fa-solid fa-cut","event":"copyCut","group":"inputBox"}],"seletctText":[{"id":"copyText","name":"复制文本","icon":"fa-solid fa-copy","event":"copyText","group":"seletctText"},{"id":"searchWord","name":"站内搜索","icon":"fa-solid fa-search","event":"OpenSearch(__text__)","group":"seletctText"},{"id":"bingSearch","name":"必应搜索","icon":"fa-solid fa-search","event":"window.open(`https://cn.bing.com/search?q=${__text__}`)","group":"seletctText"}],"elementCheck":[{"id":"openTab","name":"新标签页打开","icon":"fa-solid fa-external-link-square-alt","event":"window.open(__link__)","group":"elementCheck"},{"id":"copyLink","name":"复制链接地址","icon":"fa-solid fa-link","event":"copyLink","group":"elementCheck"}],"elementImage":[{"id":"copyImg","name":"复制图片","icon":"fa-solid fa-image","event":"copyImg","group":"elementImage"},{"id":"googleImg","name":"谷歌识图","icon":"fa-solid fa-images","event":"window.open(`https://www.google.com.hk/searchbyimage?image_url=${__link__}`)","group":"elementImage"}],"articlePage":[{"id":"printMode","name":"打印页面","icon":"fa-solid fa-print","event":"printMode","group":"articlePage"},{"id":"readMode","name":"阅读模式","icon":"fa-solid fa-book-open","event":"readMode","group":"articlePage"}]},"menus":{"link":[{"id":"help","name":"常见问题","icon":"fa-solid fa-question","link":"https://volantis.js.org/faqs/","group":"link"},{"id":"examples","name":"示例博客","icon":"fa-solid fa-rss","link":"https://volantis.js.org/examples/","group":"link"},{"id":"contributors","name":"加入社区","icon":"fa-solid fa-fan","link":"https://volantis.js.org/contributors/","group":"link"},"hr",{"id":"source_docs","name":"本站源码","icon":"fa-solid fa-code-branch","link":"https://github.com/volantis-x/volantis-docs/","group":"link"},{"id":"source_theme","name":"主题源码","icon":"fa-solid fa-code-branch","link":"https://github.com/volantis-x/hexo-theme-volantis/","group":"link"}],"darkMode":[{"id":"darkMode","name":"暗黑模式","icon":"fa-solid fa-moon","event":"volantis.dark.toggle()","group":"darkMode"}]}}
    }
  }

  /******************** volantis.EventListener ********************************/
  // 事件监听器 see: /source/js/app.js
  volantis.EventListener = {}
  // 这里存放pjax切换页面时将被移除的事件监听器
  volantis.EventListener.list = []
  //构造方法
  function volantisEventListener(type, f, ele) {
    this.type = type
    this.f = f
    this.ele = ele
  }
  // 移除事件监听器
  volantis.EventListener.remove = () => {
    volantis.EventListener.list.forEach(function (i) {
      i.ele.removeEventListener(i.type, i.f, false)
    })
    volantis.EventListener.list = []
  }
  /******************** volantis.dom.$ ********************************/
  // 注：这里没有选择器，也没有forEach一次只处理一个dom，这里重新封装主题常用的dom方法，返回的是dom对象，对象包含了以下方法，同时保留dom的原生API
  function volantisDom(ele) {
    if (!ele) ele = document.createElement("div")
    this.ele = ele;
    // ==============================================================
    this.ele.find = (c) => {
      let q = this.ele.querySelector(c)
      if (q)
        return new volantisDom(q)
    }
    // ==============================================================
    this.ele.hasClass = (c) => {
      return this.ele.className.match(new RegExp('(\\s|^)' + c + '(\\s|$)'));
    }
    this.ele.addClass = (c) => {
      this.ele.classList.add(c);
      return this.ele
    }
    this.ele.removeClass = (c) => {
      this.ele.classList.remove(c);
      return this.ele
    }
    this.ele.toggleClass = (c) => {
      if (this.ele.hasClass(c)) {
        this.ele.removeClass(c)
      } else {
        this.ele.addClass(c)
      }
      return this.ele
    }
    // ==============================================================
    // 参数 r 为 true 表示pjax切换页面时事件监听器将被移除，false不移除
    this.ele.on = (c, f, r = 1) => {
      this.ele.addEventListener(c, f, false)
      if (r) {
        volantis.EventListener.list.push(new volantisEventListener(c, f, this.ele))
      }
      return this.ele
    }
    this.ele.click = (f, r) => {
      this.ele.on("click", f, r)
      return this.ele
    }
    this.ele.scroll = (f, r) => {
      this.ele.on("scroll", f, r)
      return this.ele
    }
    // ==============================================================
    this.ele.html = (c) => {
      // if(c=== undefined){
      //   return this.ele.innerHTML
      // }else{
      this.ele.innerHTML = c
      return this.ele
      // }
    }
    // ==============================================================
    this.ele.hide = (c) => {
      this.ele.style.display = "none"
      return this.ele
    }
    this.ele.show = (c) => {
      this.ele.style.display = "block"
      return this.ele
    }
    // ==============================================================
    return this.ele
  }
  volantis.dom.$ = (ele) => {
    return !!ele ? new volantisDom(ele) : null;
  }
  /******************** RunItem ********************************/
  function RunItem() {
    this.list = []; // 存放回调函数
    this.start = () => {
      for (var i = 0; i < this.list.length; i++) {
        this.list[i].run();
      }
    };
    this.push = (fn, name, setRequestAnimationFrame = true) => {
      let myfn = fn
      if (setRequestAnimationFrame) {
        myfn = ()=>{
          volantis.requestAnimationFrame(fn)
        }
      }
      var f = new Item(myfn, name);
      this.list.push(f);
    };
    this.remove = (name) =>{
      for (let index = 0; index < this.list.length; index++) {
        const e = this.list[index];
        if (e.name == name) {
          this.list.splice(index,1);
        }
      }
    }
    // 构造一个可以run的对象
    function Item(fn, name) {
      // 函数名称
      this.name = name || fn.name;
      // run方法
      this.run = () => {
        try {
          fn()
        } catch (error) {
          console.log(error);
        }
      };
    }
  }
  /******************** Pjax ********************************/
  // /layout/_plugins/pjax/index.ejs
  // volantis.pjax.send(callBack[,"callBackName"]) 传入pjax:send回调函数
  // volantis.pjax.push(callBack[,"callBackName"]) 传入pjax:complete回调函数
  // volantis.pjax.error(callBack[,"callBackName"]) 传入pjax:error回调函数
  volantis.pjax = {};
  volantis.pjax.method = {
    complete: new RunItem(),
    error: new RunItem(),
    send: new RunItem(),
  };
  volantis.pjax = Object.assign(volantis.pjax, {
    push: volantis.pjax.method.complete.push,
    error: volantis.pjax.method.error.push,
    send: volantis.pjax.method.send.push,
  });
  /******************** RightMenu ********************************/
  // volantis.rightmenu.handle(callBack[,"callBackName"]) 外部菜单项控制
  // 可在 volantis.mouseEvent 处获取右键事件
  volantis.rightmenu = {};
  volantis.rightmenu.method = {
    handle: new RunItem(),
  }
  volantis.rightmenu = Object.assign(volantis.rightmenu, {
    handle: volantis.rightmenu.method.handle.push,
  });
  /********************  Dark Mode  ********************************/
  // /layout/_partial/scripts/darkmode.ejs
  // volantis.dark.mode 当前模式 dark or light
  // volantis.dark.toggle() 暗黑模式触发器
  // volantis.dark.push(callBack[,"callBackName"]) 传入触发器回调函数
  volantis.dark = {};
  volantis.dark.method = {
    toggle: new RunItem(),
  };
  volantis.dark = Object.assign(volantis.dark, {
    push: volantis.dark.method.toggle.push,
  });
  /********************  Message  ********************************/
  // VolantisApp.message
  /********************  isMobile  ********************************/
  // /source/js/app.js
  // volantis.isMobile
  // volantis.isMobileOld
  /********************脚本动态加载函数********************************/
  // volantis.js(src, cb)  cb 可以传入onload回调函数 或者 JSON对象 例如: volantis.js("src", ()=>{}) 或 volantis.js("src", {defer:true,onload:()=>{}})
  // volantis.css(src)

  // 返回Promise对象，如下方法同步加载资源，这利于处理文件资源之间的依赖关系，例如：APlayer 需要在 MetingJS 之前加载
  // (async () => {
  //     await volantis.js("...theme.plugins.aplayer.js.aplayer...")
  //     await volantis.js("...theme.plugins.aplayer.js.meting...")
  // })();

  // 已经加入了setTimeout
  volantis.js = (src, cb) => {
    return new Promise(resolve => {
      setTimeout(function () {
        var HEAD = document.getElementsByTagName("head")[0] || document.documentElement;
        var script = document.createElement("script");
        script.setAttribute("type", "text/javascript");
        if (cb) {
          if (JSON.stringify(cb)) {
            for (let p in cb) {
              if (p == "onload") {
                script[p] = () => {
                  cb[p]()
                  resolve()
                }
              } else {
                script[p] = cb[p]
                script.onload = resolve
              }
            }
          } else {
            script.onload = () => {
              cb()
              resolve()
            };
          }
        } else {
          script.onload = resolve
        }
        script.setAttribute("src", src);
        HEAD.appendChild(script);
      });
    });
  }
  volantis.css = (src) => {
    return new Promise(resolve => {
      setTimeout(function () {
        var link = document.createElement('link');
        link.rel = "stylesheet";
        link.href = src;
        link.onload = resolve;
        document.getElementsByTagName("head")[0].appendChild(link);
      });
    });
  }
  /********************按需加载的插件********************************/
  // volantis.import.jQuery().then(()=>{})
  volantis.import = {
    jQuery: () => {
      if (typeof jQuery == "undefined") {
        return volantis.js("https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/jquery/dist/jquery.min.js")
      } else {
        return new Promise(resolve => {
          resolve()
        });
      }
    }
  }
  /********************** requestAnimationFrame ********************************/
  // 1、requestAnimationFrame 会把每一帧中的所有 DOM 操作集中起来，在一次重绘或回流中就完成，并且重绘或回流的时间间隔紧紧跟随浏览器的刷新频率，一般来说，这个频率为每秒60帧。
  // 2、在隐藏或不可见的元素中，requestAnimationFrame 将不会进行重绘或回流，这当然就意味着更少的的 cpu，gpu 和内存使用量。
  volantis.requestAnimationFrame = (fn)=>{
    if (!window.requestAnimationFrame) {
      window.requestAnimationFrame = window.requestAnimationFrame || window.mozRequestAnimationFrame || window.webkitRequestAnimationFrame;
    }
    window.requestAnimationFrame(fn)
  }
  /************************ layoutHelper *****************************************/
  volantis.layoutHelper = (helper, html, opt)=>{
    opt = Object.assign({clean:false, pjax:true}, opt)
    function myhelper(helper, html, clean) {
      volantis.tempDiv = document.createElement("div");
      volantis.tempDiv.innerHTML = html;
      let layoutHelper = document.querySelector("#layoutHelper-"+helper)
      if (layoutHelper) {
        if (clean) {
          layoutHelper.innerHTML = ""
        }
        layoutHelper.append(volantis.tempDiv);
      }
    }
    myhelper(helper, html, opt.clean)
    if (opt.pjax) {
      volantis.pjax.push(()=>{
        myhelper(helper, html, opt.clean)
      },"layoutHelper-"+helper)
    }
  }
  /****************************** 滚动事件处理 ****************************************/
  volantis.scroll = {
    engine: new RunItem(),
    unengine: new RunItem(),
  };
  volantis.scroll = Object.assign(volantis.scroll, {
    push: volantis.scroll.engine.push,
  });
  // 滚动条距离顶部的距离
  volantis.scroll.getScrollTop = () =>{
    let scrollPos;
    if (window.pageYOffset) {
      scrollPos = window.pageYOffset;
    } else if (document.compatMode && document.compatMode != 'BackCompat') {
      scrollPos = document.documentElement.scrollTop;
    } else if (document.body) {
      scrollPos = document.body.scrollTop;
    }
    return scrollPos;
  }
  // 使用 requestAnimationFrame 处理滚动事件
  // `volantis.scroll.del` 中存储了一个数值, 该数值检测一定时间间隔内滚动条滚动的位移, 数值的检测频率是浏览器的刷新频率. 数值为正数时, 表示向下滚动. 数值为负数时, 表示向上滚动.
  volantis.scroll.handleScrollEvents = () => {
    volantis.scroll.lastScrollTop = volantis.scroll.getScrollTop()
    function loop() {
      const scrollTop = volantis.scroll.getScrollTop();
      if (volantis.scroll.lastScrollTop !== scrollTop) {
        volantis.scroll.del = scrollTop - volantis.scroll.lastScrollTop;
        volantis.scroll.lastScrollTop = scrollTop;
        // if (volantis.scroll.del > 0) {
        //   console.log("向下滚动");
        // } else {
        //   console.log("向上滚动");
        // }
        // 注销过期的unengine未滚动事件
        volantis.scroll.unengine.list=[]
        volantis.scroll.engine.start();
      }else{
        volantis.scroll.unengine.start();
      }
      volantis.requestAnimationFrame(loop)
    }
    volantis.requestAnimationFrame(loop)
  }
  volantis.scroll.handleScrollEvents()
  // 触发页面滚动至目标元素位置
  volantis.scroll.to = (ele, option = {}) =>{
    // 默认配置
    opt = {
      top: ele.getBoundingClientRect().top + document.documentElement.scrollTop,
      behavior: "smooth"
    }
    // 定义配置
    if ("top" in option) {
      opt.top = option.top
    }
    if ("behavior" in option) {
      opt.behavior = option.behavior
    }
    if ("addTop" in option) {
      opt.top += option.addTop
    }
    if (!("observerDic" in option)) {
      option.observerDic = 100
    }
    // 滚动
    window.scrollTo(opt);
    // 监视器
    // 监视并矫正元素滚动到指定位置
    // 用于处理 lazyload 引起的 cls 导致的定位失败问题
    // option.observer = false
    if (option.observer) {
      setTimeout(()=>{
        volantis.scroll.unengine.push(()=>{
          let me = ele.getBoundingClientRect().top
          if(!(me >= -option.observerDic && me <= option.observerDic)){
            volantis.scroll.to(ele, option)
          }
          volantis.scroll.unengine.remove("unengineObserver")
        },"unengineObserver")
      },1000)
    }
  }
  /********************** Content Visibility ********************************/
  // 见 source/css/first.styl 如果遇到任何问题 删除 .post-story 即可
  // 一个元素被声明 content-visibility 属性后 如果元素不在 viewport 中 浏览器不会计算其后代元素样式和属性 从而节省 Style & Layout 耗时
  // content-visibility 的副作用: 锚点失效 等等(实验初期 暂不明确), 使用此方法清除样式
  volantis.cleanContentVisibility = ()=>{
    if (document.querySelector(".post-story")) {
      console.log("cleanContentVisibility");
      document.querySelectorAll(".post-story").forEach(e=>{
        e.classList.remove("post-story")
      })
    }
  }
  /******************************************************************************/
  /******************************************************************************/
  /******************************************************************************/
  //图像加载出错时的处理
  function errorImgAvatar(img) {
    img.src = "https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/placeholder/avatar/round/3442075.svg";
    img.onerror = null;
  }
  function errorImgCover(img) {
    img.src = "https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/placeholder/cover/76b86c0226ffd.svg";
    img.onerror = null;
  }
  /******************************************************************************/
</script>

  <!-- import head_end begin -->
      <meta name="hello" content="world">
  <!-- import head_end end -->
  <!-- Custom Files headEnd begin-->
  
  <!-- Custom Files headEnd end-->
</head>
  <body itemscope itemtype="http://schema.org/WebPage">
    <!-- import body_begin begin-->
        <script>var _hmt=_hmt||[];(function(){var hm=document.createElement("script");hm.src="https://hm.baidu.com/hm.js?6c135fd66381144e9a12813c76e66092";var s=document.getElementsByTagName("script")[0];s.parentNode.insertBefore(hm,s)})();</script>
        <!--Global site tag(gtag.js)-Google Analytics--><script async src="https://www.googletagmanager.com/gtag/js?id=UA-149674019-2"></script><script>window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments)}gtag('js',new Date());gtag('config','UA-149674019-2');</script>
        <script src="https://v1.hitokoto.cn/?encode=js&select=%23hitokoto" defer></script>
    <!-- import body_begin end-->
    <!-- Custom Files bodyBegin begin-->
    
    <!-- Custom Files bodyBegin end-->
    <header itemscope itemtype="http://schema.org/WPHeader" id="l_header" class="l_header auto shadow floatable blur show" style='opacity: 0' >
  <div class='container'>
  <div id='wrapper'>
    <div class='nav-sub'>
      <p class="title"></p>
      <ul class='switcher nav-list-h m-phone' id="pjax-header-nav-list">
        <li><a id="s-comment" class="fa-solid fa-comments fa-fw" target="_self"  href="/" onclick="return false;" title="comment"></a></li>
        
          <li><a id="s-toc" class="s-toc fa-solid fa-list fa-fw" target="_self"  href="/" onclick="return false;" title="toc"></a></li>
        
      </ul>
    </div>
		<div class="nav-main">
      
        
        <a class="title flat-box" target="_self" href='/'>
          
          
            <i class='https://imagecdn.bosong.online/image/google.svg'></i>
          
          
            星空
          
        </a>
      

			<div class='menu navigation'>
				<ul class='nav-list-h m-pc'>
          
          
          
            
            
              <li>
                <a class="menuitem flat-box faa-parent animated-hover"
                href="/" title="博客"
                  
                  
                  
                    active-action="action-home"
                  >
                  <i class='fa-solid fa-home fa-fw'></i>博客
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="menuitem flat-box faa-parent animated-hover"
                href="/archives/" title="归档"
                  
                  
                  
                    active-action="action-archives"
                  >
                  <i class='fa-solid fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="menuitem flat-box faa-parent animated-hover"
                href="/about/" title="是我"
                  
                  
                  
                    active-action="action-about"
                  >
                  <i class='fa-solid fa-info-circle fa-fw'></i>是我
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="menuitem flat-box header toggle-mode-btn">
                  <i class='fa-solid fa-moon fa-fw'></i>夜色很美
                </a>
              <li>
            
          
          
				</ul>
			</div>
      
      <div class="m_search">
        <form name="searchform" class="form u-search-form">
          <i class="icon fa-solid fa-search fa-fw"></i>
          <input type="text" class="input u-search-input" placeholder="Search..." />
        </form>
      </div>
      

			<ul class='switcher nav-list-h m-phone'>
				
					<li><a class="s-search fa-solid fa-search fa-fw" target="_self" href="/" onclick="return false;" title="search"></a></li>
				
				<li>
          <a class="s-menu fa-solid fa-bars fa-fw" target="_self" href="/" onclick="return false;" title="menu"></a>
          <ul class="menu-phone list-v navigation white-box">
            
              
            
              <li>
                <a class="menuitem flat-box faa-parent animated-hover"
                href="/" title="博客"
                  
                  
                  
                    active-action="action-home"
                  >
                  <i class='fa-solid fa-home fa-fw'></i>博客
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="menuitem flat-box faa-parent animated-hover"
                href="/archives/" title="归档"
                  
                  
                  
                    active-action="action-archives"
                  >
                  <i class='fa-solid fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="menuitem flat-box faa-parent animated-hover"
                href="/about/" title="是我"
                  
                  
                  
                    active-action="action-about"
                  >
                  <i class='fa-solid fa-info-circle fa-fw'></i>是我
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="menuitem flat-box header toggle-mode-btn">
                  <i class='fa-solid fa-moon fa-fw'></i>夜色很美
                </a>
              <li>
            
          
            
          </ul>
        </li>
			</ul>

      <!-- Custom Files header begin -->
      
      <!-- Custom Files header end -->
		</div>
	</div>
  </div>
</header>

    <div id="l_body">
      <div id="l_cover">
  
    
      <!-- see: /layout/_partial/scripts/_ctrl/coverCtrl.ejs -->
      <div id="none" class='cover-wrapper post dock' style="display: none;">
        
  <div class='cover-bg lazyload placeholder' data-bg="https://uploadbeta.com/api/pictures/random/?key=BingEverydayWallpaperPicture"></div>

<div class='cover-body'>
  <div class='top'>
    
    
      <p class="title">Volantis</p>
    
    
  </div>
  <div class='bottom'>
    <div class='menu navigation'>
      <div class='list-h'>
        
          
            <a target="_blank" rel="noopener" href="https://github.com/volantis-x/hexo-theme-volantis/"
              
              
              active-action="action-https:githubcomvolantis-xhexo-theme-volantis">
              <img src='https://cdn.jsdelivr.net/gh/twitter/twemoji@13.0/assets/svg/1f9ec.svg'><p>源码</p>
            </a>
          
        
      </div>
    </div>
  </div>
</div>

        <div id="scroll-down" style="display: none;"><i class="fa fa-chevron-down scroll-down-effects"></i></div>
      </div>
    
  
</div>

      <div id="safearea">
        <div class="body-wrapper">
          
<div id="l_main" class=''>
  <article itemscope itemtype="http://schema.org/Article" class="article post white-box reveal md shadow floatable blur article-type-post" id="post" itemscope itemprop="blogPost">
  <link itemprop="mainEntityOfPage" href="https://blog.bosong.online/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html">
  <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
    <meta itemprop="name" content="星空">
  </span>
  <span hidden itemprop="post" itemscope itemtype="http://schema.org/Post">
    <meta itemprop="name" content="星空">
    <meta itemprop="description" content="Jack，现居于北京朝阳，专注于Devops 技术人生 Java Golang 微服务 云计算，这是Jack的个人博客站">
  </span>
  


  
    <span hidden>
      <meta itemprop="image" content="https://imagecdn.bosong.online/image/google.svg">
    </span>
  
  <div class="article-meta" id="top">
    
    
    
      <h1 class="title" itemprop="name headline">
        ClickHouse常用函数
      </h1>
      <div class='new-meta-box'>
        
          
            
<div class='new-meta-item author' itemprop="author" itemscope itemtype="http://schema.org/Person">
  <a itemprop="url" class='author' href="/" rel="nofollow">
    <img itemprop="image" src="https://imagecdn.bosong.online/image/avatar.jpg" class="lazyload" data-srcset="https://imagecdn.bosong.online/image/avatar.jpg" srcset="">
    <p itemprop="name">jack</p>
  </a>
</div>

          
        
          
            
  <div class='new-meta-item category'>
    <i class="fa-solid fa-folder-open fa-fw" aria-hidden="true"></i>
    <a class="category-link" href="/categories/ClickHouse/">ClickHouse</a>
    
      <span hidden itemprop="about" itemscope itemtype="http://schema.org/Thing">
        <a href="/categories/ClickHouse/" itemprop="url"><span itemprop="name">ClickHouse</span></a>
      </span>
    
  </div>


          
        
          
            <div class="new-meta-item date" itemprop="dateCreated datePublished" datetime="2021-03-18T20:47:45+08:00">
  <a class='notlink'>
    <i class="fa-solid fa-calendar-alt fa-fw" aria-hidden="true"></i>
    <p>发布于：2021年3月18日</p>
  </a>
</div>

          
        
          
            

          
        
        <!-- Custom Files topMeta begin-->
        
        <!-- Custom Files topMeta end-->
      </div>
    
  </div>


  <div id="layoutHelper-page-plugins"></div>
  <div id="post-body" itemprop="articleBody">
    <div class="story post-story"><h2 id="前言"><a href="#前言" class="headerlink" title="前言"></a>前言</h2><p>近期项目引入了Clickhouse进行数据计算与展示，今天突然我们的PM同学来问我一个问题，怎么快速获取这十个品牌中GMV各自排名前三的SPU数据。当时想了想，一般情况下这种需求都是使用代码实现的，简直So easy，但是一旦写代码的话就没法快速获取并且以后类似的需求也没法快速响应了。</p>
<p>然后想到了咱们使用的是Clickhouse数据库，它自带很多函数，在经过一段时间的查询以后，发现有一个函数还是比较好用的，正好解决这种典型的<em>分组取TOP</em>的问题，这个函数就是<code>groupArray(x)</code>，可以查看以下sql，看它是怎么使用的：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">select * from  </span><br><span class="line">(select b.bid,b.brand as brand, c[1] as top1,c[2] as top2,c[3] as top3,groupArray(3)(b.spu_name) as c,groupArray(10)(b.gmv) as d,d[1]+d[2]+d[3]+d[4]+d[5]+d[6]+d[7]+d[8]+d[9]+d[10] as gmvsum  </span><br><span class="line">from (  </span><br><span class="line">      select spu_id,spu_name,gmv,us,brand,bid  </span><br><span class="line">  from spu  </span><br><span class="line">      where platform_id = 2  </span><br><span class="line">  and c2_id = 3305  </span><br><span class="line">  and bid in (1234, 5678, 1903, 41313, 3145, 555, 111, 444, 222, 444)  </span><br><span class="line">        and multiSearchAny(spu_name, [&#x27;精酿啤酒&#x27;,&#x27;啤酒精酿&#x27;])  </span><br><span class="line">        and not multiSearchAny(spu_name, [&#x27;饮料&#x27;])  </span><br><span class="line">        and effective_date between &#x27;2020-09-30&#x27; and &#x27;2021-02-28&#x27;  </span><br><span class="line">  order by gmv desc)  </span><br><span class="line">    b group by b.bid,b.brand order by bid) c order by c.gmvsum desc;</span><br></pre></td></tr></table></figure>

</div><div class="story post-story"><h2 id="Clickhouse的简单介绍"><a href="#Clickhouse的简单介绍" class="headerlink" title="Clickhouse的简单介绍"></a>Clickhouse的简单介绍</h2><p>ClickHouse是一个用于<em>联机分析(OLAP)<em>的</em>列式</em>数据库管理系统(DBMS)；</p>
<p>Mysql等数据库是将数据以<em>行的形式</em>来进行存储，它是标准的行式数据库，但是在列式数据库中数据以<em>列的形式</em>来进行存储;<br>以列的形式来存储数据，它还是有不少优点的，主要是：</p>
<ul>
<li>绝大多数是读请求</li>
<li>数据以相当大的批次(&gt; 1000行)更新，而不是单行更新;或者根本没有更新。</li>
<li>已添加到数据库的数据不能修改。</li>
<li>对于读取，从数据库中提取相当多的行，但只提取列的一小部分。</li>
<li>宽表，即每个表包含着大量的列</li>
<li>查询相对较少(通常每台服务器每秒查询数百次或更少)</li>
<li>对于简单查询，允许延迟大约50毫秒</li>
<li>列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)</li>
<li>处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)</li>
<li>事务不是必须的</li>
<li>对数据一致性要求低</li>
<li>每个查询有一个大表。除了他以外，其他的都很小。</li>
<li>查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中</li>
</ul>
<p>可以根据官方文档来看一下OLAP的列式数据库查询为什么比行式数据库查询效率要高：<br>行式数据库</p>
<p><img src="https://imagecdn.bosong.online/blog/row-oriented.gif" class="lazyload" data-srcset="https://imagecdn.bosong.online/blog/row-oriented.gif" srcset="" alt="行式数据库"></p>
<p>列式数据库</p>
<p><img src="https://imagecdn.bosong.online/blog/column-oriented.gif" class="lazyload" data-srcset="https://imagecdn.bosong.online/blog/column-oriented.gif" srcset="" alt="列式数据库"></p>
<p>一般情况下，在数据计算时只需要抽取其中一部分列，所以使用列式数据库相比较来说会快速的查找到需要的列，相比行式数据库来说减少了大量的IO。<br>当然还有一些其他方面的优势，可以参考<a target="_blank" rel="noopener" href="https://clickhouse.tech/docs/zh/">官方的文档</a>。</p>
</div><div class="story post-story"><h2 id="Clickhouse函数指南"><a href="#Clickhouse函数指南" class="headerlink" title="Clickhouse函数指南"></a>Clickhouse函数指南</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br><span class="line">306</span><br><span class="line">307</span><br><span class="line">308</span><br><span class="line">309</span><br><span class="line">310</span><br><span class="line">311</span><br><span class="line">312</span><br><span class="line">313</span><br><span class="line">314</span><br><span class="line">315</span><br><span class="line">316</span><br><span class="line">317</span><br><span class="line">318</span><br><span class="line">319</span><br><span class="line">320</span><br><span class="line">321</span><br><span class="line">322</span><br><span class="line">323</span><br><span class="line">324</span><br><span class="line">325</span><br><span class="line">326</span><br><span class="line">327</span><br><span class="line">328</span><br><span class="line">329</span><br><span class="line">330</span><br><span class="line">331</span><br><span class="line">332</span><br><span class="line">333</span><br><span class="line">334</span><br><span class="line">335</span><br><span class="line">336</span><br><span class="line">337</span><br><span class="line">338</span><br><span class="line">339</span><br><span class="line">340</span><br><span class="line">341</span><br><span class="line">342</span><br><span class="line">343</span><br><span class="line">344</span><br><span class="line">345</span><br><span class="line">346</span><br><span class="line">347</span><br><span class="line">348</span><br><span class="line">349</span><br><span class="line">350</span><br><span class="line">351</span><br><span class="line">352</span><br><span class="line">353</span><br><span class="line">354</span><br><span class="line">355</span><br><span class="line">356</span><br><span class="line">357</span><br><span class="line">358</span><br><span class="line">359</span><br><span class="line">360</span><br><span class="line">361</span><br><span class="line">362</span><br><span class="line">363</span><br><span class="line">364</span><br><span class="line">365</span><br><span class="line">366</span><br><span class="line">367</span><br><span class="line">368</span><br><span class="line">369</span><br><span class="line">370</span><br><span class="line">371</span><br><span class="line">372</span><br><span class="line">373</span><br><span class="line">374</span><br><span class="line">375</span><br><span class="line">376</span><br><span class="line">377</span><br><span class="line">378</span><br><span class="line">379</span><br><span class="line">380</span><br><span class="line">381</span><br><span class="line">382</span><br><span class="line">383</span><br><span class="line">384</span><br><span class="line">385</span><br><span class="line">386</span><br><span class="line">387</span><br><span class="line">388</span><br><span class="line">389</span><br><span class="line">390</span><br><span class="line">391</span><br><span class="line">392</span><br><span class="line">393</span><br><span class="line">394</span><br><span class="line">395</span><br><span class="line">396</span><br><span class="line">397</span><br><span class="line">398</span><br><span class="line">399</span><br><span class="line">400</span><br><span class="line">401</span><br><span class="line">402</span><br><span class="line">403</span><br><span class="line">404</span><br><span class="line">405</span><br><span class="line">406</span><br><span class="line">407</span><br><span class="line">408</span><br><span class="line">409</span><br><span class="line">410</span><br><span class="line">411</span><br><span class="line">412</span><br><span class="line">413</span><br><span class="line">414</span><br><span class="line">415</span><br><span class="line">416</span><br><span class="line">417</span><br><span class="line">418</span><br><span class="line">419</span><br><span class="line">420</span><br><span class="line">421</span><br><span class="line">422</span><br><span class="line">423</span><br><span class="line">424</span><br><span class="line">425</span><br><span class="line">426</span><br><span class="line">427</span><br><span class="line">428</span><br><span class="line">429</span><br><span class="line">430</span><br><span class="line">431</span><br><span class="line">432</span><br><span class="line">433</span><br><span class="line">434</span><br><span class="line">435</span><br><span class="line">436</span><br><span class="line">437</span><br><span class="line">438</span><br><span class="line">439</span><br><span class="line">440</span><br><span class="line">441</span><br><span class="line">442</span><br><span class="line">443</span><br><span class="line">444</span><br><span class="line">445</span><br><span class="line">446</span><br><span class="line">447</span><br><span class="line">448</span><br><span class="line">449</span><br><span class="line">450</span><br><span class="line">451</span><br><span class="line">452</span><br><span class="line">453</span><br><span class="line">454</span><br><span class="line">455</span><br><span class="line">456</span><br><span class="line">457</span><br><span class="line">458</span><br><span class="line">459</span><br><span class="line">460</span><br><span class="line">461</span><br><span class="line">462</span><br><span class="line">463</span><br><span class="line">464</span><br><span class="line">465</span><br><span class="line">466</span><br><span class="line">467</span><br><span class="line">468</span><br><span class="line">469</span><br><span class="line">470</span><br><span class="line">471</span><br><span class="line">472</span><br><span class="line">473</span><br><span class="line">474</span><br><span class="line">475</span><br><span class="line">476</span><br><span class="line">477</span><br><span class="line">478</span><br><span class="line">479</span><br><span class="line">480</span><br><span class="line">481</span><br><span class="line">482</span><br><span class="line">483</span><br><span class="line">484</span><br><span class="line">485</span><br><span class="line">486</span><br><span class="line">487</span><br><span class="line">488</span><br><span class="line">489</span><br><span class="line">490</span><br><span class="line">491</span><br><span class="line">492</span><br><span class="line">493</span><br><span class="line">494</span><br><span class="line">495</span><br><span class="line">496</span><br><span class="line">497</span><br><span class="line">498</span><br><span class="line">499</span><br><span class="line">500</span><br><span class="line">501</span><br><span class="line">502</span><br><span class="line">503</span><br><span class="line">504</span><br><span class="line">505</span><br><span class="line">506</span><br><span class="line">507</span><br><span class="line">508</span><br><span class="line">509</span><br><span class="line">510</span><br><span class="line">511</span><br><span class="line">512</span><br><span class="line">513</span><br><span class="line">514</span><br><span class="line">515</span><br><span class="line">516</span><br><span class="line">517</span><br><span class="line">518</span><br><span class="line">519</span><br><span class="line">520</span><br><span class="line">521</span><br><span class="line">522</span><br><span class="line">523</span><br><span class="line">524</span><br><span class="line">525</span><br><span class="line">526</span><br><span class="line">527</span><br><span class="line">528</span><br><span class="line">529</span><br><span class="line">530</span><br><span class="line">531</span><br><span class="line">532</span><br><span class="line">533</span><br><span class="line">534</span><br><span class="line">535</span><br><span class="line">536</span><br><span class="line">537</span><br><span class="line">538</span><br><span class="line">539</span><br><span class="line">540</span><br><span class="line">541</span><br><span class="line">542</span><br><span class="line">543</span><br><span class="line">544</span><br><span class="line">545</span><br><span class="line">546</span><br><span class="line">547</span><br><span class="line">548</span><br><span class="line">549</span><br><span class="line">550</span><br><span class="line">551</span><br><span class="line">552</span><br><span class="line">553</span><br><span class="line">554</span><br><span class="line">555</span><br><span class="line">556</span><br><span class="line">557</span><br><span class="line">558</span><br><span class="line">559</span><br><span class="line">560</span><br><span class="line">561</span><br><span class="line">562</span><br><span class="line">563</span><br><span class="line">564</span><br><span class="line">565</span><br><span class="line">566</span><br><span class="line">567</span><br><span class="line">568</span><br><span class="line">569</span><br><span class="line">570</span><br><span class="line">571</span><br><span class="line">572</span><br><span class="line">573</span><br><span class="line">574</span><br><span class="line">575</span><br><span class="line">576</span><br><span class="line">577</span><br><span class="line">578</span><br><span class="line">579</span><br><span class="line">580</span><br><span class="line">581</span><br><span class="line">582</span><br><span class="line">583</span><br><span class="line">584</span><br><span class="line">585</span><br><span class="line">586</span><br><span class="line">587</span><br><span class="line">588</span><br><span class="line">589</span><br><span class="line">590</span><br><span class="line">591</span><br><span class="line">592</span><br><span class="line">593</span><br><span class="line">594</span><br><span class="line">595</span><br><span class="line">596</span><br><span class="line">597</span><br><span class="line">598</span><br><span class="line">599</span><br><span class="line">600</span><br><span class="line">601</span><br><span class="line">602</span><br><span class="line">603</span><br><span class="line">604</span><br><span class="line">605</span><br><span class="line">606</span><br><span class="line">607</span><br><span class="line">608</span><br><span class="line">609</span><br><span class="line">610</span><br><span class="line">611</span><br><span class="line">612</span><br><span class="line">613</span><br><span class="line">614</span><br><span class="line">615</span><br><span class="line">616</span><br><span class="line">617</span><br><span class="line">618</span><br><span class="line">619</span><br><span class="line">620</span><br><span class="line">621</span><br><span class="line">622</span><br><span class="line">623</span><br><span class="line">624</span><br><span class="line">625</span><br><span class="line">626</span><br><span class="line">627</span><br><span class="line">628</span><br><span class="line">629</span><br><span class="line">630</span><br><span class="line">631</span><br><span class="line">632</span><br><span class="line">633</span><br><span class="line">634</span><br><span class="line">635</span><br><span class="line">636</span><br><span class="line">637</span><br><span class="line">638</span><br><span class="line">639</span><br><span class="line">640</span><br><span class="line">641</span><br><span class="line">642</span><br><span class="line">643</span><br><span class="line">644</span><br><span class="line">645</span><br><span class="line">646</span><br><span class="line">647</span><br><span class="line">648</span><br><span class="line">649</span><br><span class="line">650</span><br><span class="line">651</span><br><span class="line">652</span><br><span class="line">653</span><br><span class="line">654</span><br><span class="line">655</span><br><span class="line">656</span><br><span class="line">657</span><br><span class="line">658</span><br><span class="line">659</span><br><span class="line">660</span><br><span class="line">661</span><br><span class="line">662</span><br><span class="line">663</span><br><span class="line">664</span><br><span class="line">665</span><br><span class="line">666</span><br><span class="line">667</span><br><span class="line">668</span><br><span class="line">669</span><br><span class="line">670</span><br><span class="line">671</span><br><span class="line">672</span><br><span class="line">673</span><br><span class="line">674</span><br><span class="line">675</span><br><span class="line">676</span><br><span class="line">677</span><br><span class="line">678</span><br><span class="line">679</span><br><span class="line">680</span><br><span class="line">681</span><br><span class="line">682</span><br><span class="line">683</span><br><span class="line">684</span><br><span class="line">685</span><br><span class="line">686</span><br><span class="line">687</span><br><span class="line">688</span><br><span class="line">689</span><br><span class="line">690</span><br><span class="line">691</span><br><span class="line">692</span><br><span class="line">693</span><br><span class="line">694</span><br><span class="line">695</span><br><span class="line">696</span><br><span class="line">697</span><br><span class="line">698</span><br><span class="line">699</span><br><span class="line">700</span><br><span class="line">701</span><br><span class="line">702</span><br><span class="line">703</span><br><span class="line">704</span><br><span class="line">705</span><br><span class="line">706</span><br><span class="line">707</span><br><span class="line">708</span><br><span class="line">709</span><br><span class="line">710</span><br><span class="line">711</span><br><span class="line">712</span><br><span class="line">713</span><br><span class="line">714</span><br><span class="line">715</span><br><span class="line">716</span><br><span class="line">717</span><br><span class="line">718</span><br><span class="line">719</span><br><span class="line">720</span><br><span class="line">721</span><br><span class="line">722</span><br><span class="line">723</span><br><span class="line">724</span><br><span class="line">725</span><br><span class="line">726</span><br><span class="line">727</span><br><span class="line">728</span><br><span class="line">729</span><br><span class="line">730</span><br><span class="line">731</span><br><span class="line">732</span><br><span class="line">733</span><br><span class="line">734</span><br><span class="line">735</span><br><span class="line">736</span><br><span class="line">737</span><br><span class="line">738</span><br><span class="line">739</span><br><span class="line">740</span><br><span class="line">741</span><br><span class="line">742</span><br><span class="line">743</span><br><span class="line">744</span><br><span class="line">745</span><br><span class="line">746</span><br><span class="line">747</span><br><span class="line">748</span><br><span class="line">749</span><br><span class="line">750</span><br><span class="line">751</span><br><span class="line">752</span><br><span class="line">753</span><br><span class="line">754</span><br><span class="line">755</span><br><span class="line">756</span><br><span class="line">757</span><br><span class="line">758</span><br><span class="line">759</span><br><span class="line">760</span><br><span class="line">761</span><br><span class="line">762</span><br><span class="line">763</span><br><span class="line">764</span><br><span class="line">765</span><br><span class="line">766</span><br><span class="line">767</span><br><span class="line">768</span><br><span class="line">769</span><br><span class="line">770</span><br><span class="line">771</span><br><span class="line">772</span><br><span class="line">773</span><br><span class="line">774</span><br><span class="line">775</span><br><span class="line">776</span><br><span class="line">777</span><br><span class="line">778</span><br><span class="line">779</span><br><span class="line">780</span><br><span class="line">781</span><br><span class="line">782</span><br><span class="line">783</span><br><span class="line">784</span><br><span class="line">785</span><br><span class="line">786</span><br><span class="line">787</span><br><span class="line">788</span><br><span class="line">789</span><br><span class="line">790</span><br><span class="line">791</span><br><span class="line">792</span><br><span class="line">793</span><br><span class="line">794</span><br><span class="line">795</span><br><span class="line">796</span><br><span class="line">797</span><br><span class="line">798</span><br><span class="line">799</span><br><span class="line">800</span><br><span class="line">801</span><br><span class="line">802</span><br><span class="line">803</span><br><span class="line">804</span><br><span class="line">805</span><br><span class="line">806</span><br><span class="line">807</span><br><span class="line">808</span><br><span class="line">809</span><br><span class="line">810</span><br><span class="line">811</span><br><span class="line">812</span><br><span class="line">813</span><br><span class="line">814</span><br><span class="line">815</span><br><span class="line">816</span><br><span class="line">817</span><br><span class="line">818</span><br><span class="line">819</span><br><span class="line">820</span><br><span class="line">821</span><br><span class="line">822</span><br><span class="line">823</span><br><span class="line">824</span><br><span class="line">825</span><br><span class="line">826</span><br><span class="line">827</span><br><span class="line">828</span><br><span class="line">829</span><br><span class="line">830</span><br><span class="line">831</span><br><span class="line">832</span><br><span class="line">833</span><br><span class="line">834</span><br><span class="line">835</span><br><span class="line">836</span><br><span class="line">837</span><br><span class="line">838</span><br><span class="line">839</span><br><span class="line">840</span><br><span class="line">841</span><br><span class="line">842</span><br><span class="line">843</span><br><span class="line">844</span><br><span class="line">845</span><br><span class="line">846</span><br><span class="line">847</span><br><span class="line">848</span><br><span class="line">849</span><br><span class="line">850</span><br><span class="line">851</span><br><span class="line">852</span><br><span class="line">853</span><br><span class="line">854</span><br><span class="line">855</span><br><span class="line">856</span><br><span class="line">857</span><br><span class="line">858</span><br><span class="line">859</span><br><span class="line">860</span><br><span class="line">861</span><br><span class="line">862</span><br><span class="line">863</span><br><span class="line">864</span><br><span class="line">865</span><br><span class="line">866</span><br><span class="line">867</span><br><span class="line">868</span><br><span class="line">869</span><br><span class="line">870</span><br><span class="line">871</span><br><span class="line">872</span><br><span class="line">873</span><br><span class="line">874</span><br><span class="line">875</span><br><span class="line">876</span><br><span class="line">877</span><br><span class="line">878</span><br><span class="line">879</span><br><span class="line">880</span><br><span class="line">881</span><br><span class="line">882</span><br><span class="line">883</span><br><span class="line">884</span><br><span class="line">885</span><br><span class="line">886</span><br><span class="line">887</span><br><span class="line">888</span><br><span class="line">889</span><br><span class="line">890</span><br><span class="line">891</span><br><span class="line">892</span><br><span class="line">893</span><br><span class="line">894</span><br><span class="line">895</span><br><span class="line">896</span><br><span class="line">897</span><br><span class="line">898</span><br><span class="line">899</span><br><span class="line">900</span><br><span class="line">901</span><br><span class="line">902</span><br><span class="line">903</span><br><span class="line">904</span><br><span class="line">905</span><br><span class="line">906</span><br><span class="line">907</span><br><span class="line">908</span><br><span class="line">909</span><br><span class="line">910</span><br><span class="line">911</span><br><span class="line">912</span><br><span class="line">913</span><br><span class="line">914</span><br><span class="line">915</span><br><span class="line">916</span><br><span class="line">917</span><br><span class="line">918</span><br><span class="line">919</span><br><span class="line">920</span><br><span class="line">921</span><br><span class="line">922</span><br><span class="line">923</span><br><span class="line">924</span><br><span class="line">925</span><br><span class="line">926</span><br><span class="line">927</span><br><span class="line">928</span><br><span class="line">929</span><br><span class="line">930</span><br><span class="line">931</span><br><span class="line">932</span><br><span class="line">933</span><br><span class="line">934</span><br><span class="line">935</span><br><span class="line">936</span><br><span class="line">937</span><br><span class="line">938</span><br><span class="line">939</span><br><span class="line">940</span><br><span class="line">941</span><br><span class="line">942</span><br><span class="line">943</span><br><span class="line">944</span><br><span class="line">945</span><br><span class="line">946</span><br><span class="line">947</span><br><span class="line">948</span><br><span class="line">949</span><br><span class="line">950</span><br><span class="line">951</span><br><span class="line">952</span><br><span class="line">953</span><br><span class="line">954</span><br><span class="line">955</span><br><span class="line">956</span><br><span class="line">957</span><br><span class="line">958</span><br><span class="line">959</span><br><span class="line">960</span><br><span class="line">961</span><br><span class="line">962</span><br><span class="line">963</span><br><span class="line">964</span><br><span class="line">965</span><br><span class="line">966</span><br><span class="line">967</span><br><span class="line">968</span><br><span class="line">969</span><br><span class="line">970</span><br><span class="line">971</span><br><span class="line">972</span><br><span class="line">973</span><br><span class="line">974</span><br><span class="line">975</span><br><span class="line">976</span><br><span class="line">977</span><br><span class="line">978</span><br><span class="line">979</span><br><span class="line">980</span><br><span class="line">981</span><br><span class="line">982</span><br><span class="line">983</span><br><span class="line">984</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">-- 零、检测函数类型（clickhouse中数据的类型）</span><br><span class="line">SELECT toTypeName(0);-- UInt8(三位数为8)</span><br><span class="line">SELECT toTypeName(-0);-- Int8</span><br><span class="line">SELECT toTypeName(-343);-- Int16</span><br><span class="line">SELECT toTypeName(12.43); -- Float64(默认浮点型的数据为64)，所以一般在处理浮点型的数据的时候尽量转成toFloat32(12.43)</span><br><span class="line">SELECT toTypeName(12.34343); -- Float64</span><br><span class="line">SELECT toTypeName(toDateTime(1502396027)); -- DateTime</span><br><span class="line">-- 一、算数函数</span><br><span class="line">--&gt;&gt;&gt;&gt;&gt;&gt; 算数函数(数学上的计算)</span><br><span class="line">--求和</span><br><span class="line">SELECT plus(12, 21), plus(10, -10), plus(-10, -10);</span><br><span class="line">--差值</span><br><span class="line">SELECT minus(10, 5), minus(10, -10),minus(-10, -10);</span><br><span class="line">--积</span><br><span class="line">SELECT multiply(12, 2), multiply(12, -2), multiply(-12, -2);</span><br><span class="line">--平均值</span><br><span class="line">SELECT divide(12, 4), divide(10, 3), divide(2, 4), divide(-4, -2), divide(-4, 2), divide(-4.5, 3);</span><br><span class="line">SELECT intDiv(10, 3), divide(10, 3); -- 3, 3.333(保留四位有效数字)</span><br><span class="line">SELECT divide(10, 0), divide(-10, 0); -- 出现无穷大字符“ ∞ ”或“ -∞ ”</span><br><span class="line">SELECT divide(0, 0); -- 特殊字符（类似乱码）</span><br><span class="line">SELECT intDivOrZero(10, 0); -- 0</span><br><span class="line">--求余数</span><br><span class="line">SELECT modulo(10, 3); --1</span><br><span class="line">SELECT modulo(10.5, 3); --1</span><br><span class="line">--取反</span><br><span class="line">SELECT negate(10), negate(-10); -- -10	10</span><br><span class="line">--绝对值</span><br><span class="line">SELECT abs(-10), abs(10);</span><br><span class="line">--最大公约数</span><br><span class="line">SELECT gcd(12, 24), gcd(-12, -24), gcd(-12, 24);</span><br><span class="line">--最小公倍数</span><br><span class="line">SELECT lcm(12, 24), lcm(-12, -24), lcm(-3, 4);</span><br><span class="line">-- 二、比较函数</span><br><span class="line">--&gt;&gt;&gt;&gt;&gt;&gt; 比较函数(始终返回0表示false 或   1表示true)</span><br><span class="line">SELECT 12 == 12, 12 != 10, 12 == 132, 12 != 12, 12 &lt;&gt; 12;</span><br><span class="line">SELECT equals(12, 12), notEquals(12, 10), equals(12, 10), notEquals(12,123);</span><br><span class="line">SELECT greater(12, 10), greater(10, 12), greater(12, 12);-- 前者是否大于后者</span><br><span class="line">SELECT greaterOrEquals(12,10), greaterOrEquals(12,12);-- 前者是否大于或等于后者</span><br><span class="line">SELECT less(12, 21), less(12, 10), less(120, 120);-- 前者是否小于后者</span><br><span class="line">SELECT lessOrEquals(12, 120), lessOrEquals(12, 12);-- 前世是否小于或等于或者</span><br><span class="line">-- 三、逻辑函数</span><br><span class="line">--&gt;&gt;&gt;&gt;&gt;&gt; 逻辑操作符（返回0表示false 或 1表示true）</span><br><span class="line">SELECT 12==12 or 12!=10;</span><br><span class="line">SELECT 12==12 and 12!=10;</span><br><span class="line">SELECT not 12, not 0;</span><br><span class="line">SELECT or(equals(12, 12), notEquals(12, 10)); --函数表示法：或</span><br><span class="line">SELECT and(equals(12, 12), notEquals(12, 10));--函数表示法：且</span><br><span class="line">SELECT not(12), not(0);</span><br><span class="line">-- 四、类型转换函数</span><br><span class="line">--&gt;&gt;&gt;&gt;&gt;&gt; 类型转换函数部分示例：</span><br><span class="line">SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);</span><br><span class="line">SELECT toString(now());</span><br><span class="line">SELECT now() AS now_local, toString(now(), &#x27;Asia/Yekaterinburg&#x27;) AS now_yekat;</span><br><span class="line">SELECT now() AS now_local, toDate(now()), toDateTime(now()), toUnixTimestamp(now());</span><br><span class="line">SELECT</span><br><span class="line">&#x27;2016-06-15 23:00:00&#x27; AS timestamp,</span><br><span class="line">CAST(timestamp AS DateTime) AS datetime,</span><br><span class="line">CAST(timestamp AS Date) AS date,</span><br><span class="line">CAST(timestamp, &#x27;String&#x27;) AS string,</span><br><span class="line">CAST(timestamp, &#x27;FixedString(22)&#x27;) AS fixed_string;</span><br><span class="line">WITH</span><br><span class="line">   toDate(&#x27;2019-01-01&#x27;) AS date,</span><br><span class="line">   INTERVAL 1 WEEK AS interval_week,</span><br><span class="line">   toIntervalWeek(1) AS interval_to_week,</span><br><span class="line">   toIntervalMonth(1) AS interval_to_month</span><br><span class="line">SELECT</span><br><span class="line">   date + interval_week,</span><br><span class="line">   date + interval_to_week,</span><br><span class="line">   date + interval_to_month;</span><br><span class="line">WITH</span><br><span class="line">	toDateTime(&#x27;2019-01-01 12:10:10&#x27;) as datetime,</span><br><span class="line">	INTERVAL 1 HOUR AS interval_hour,</span><br><span class="line">	toIntervalHour(1) as invterval_to_hour</span><br><span class="line">SELECT</span><br><span class="line">	plus(datetime, interval_hour),</span><br><span class="line">	plus(datetime, invterval_to_hour);</span><br><span class="line">-- 五、时间日期函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 时间日期函数</span><br><span class="line">SELECT</span><br><span class="line">	toDateTime(&#x27;2019-07-30 10:10:10&#x27;) AS time,</span><br><span class="line">	-- 将DateTime转换成Unix时间戳</span><br><span class="line">	toUnixTimestamp(time) as unixTimestamp,</span><br><span class="line">	-- 保留  时-分-秒</span><br><span class="line">	toDate(time) as date_local,</span><br><span class="line">	toTime(time) as date_time,-- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。</span><br><span class="line">	-- 获取年份，月份，季度，小时，分钟，秒钟</span><br><span class="line">	toYear(time) as get_year,</span><br><span class="line">	toMonth(time) as get_month,</span><br><span class="line">	-- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）</span><br><span class="line">	toQuarter(time) as get_quarter,</span><br><span class="line">	toHour(time) as get_hour,</span><br><span class="line">	toMinute(time) as get_minute,</span><br><span class="line">	toSecond(time) as get_second,</span><br><span class="line">	-- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几</span><br><span class="line">	toDayOfYear(time) as &quot;当前年份中的第几天&quot;,</span><br><span class="line">	toDayOfMonth(time) as &quot;当前月份的第几天&quot;,</span><br><span class="line">	toDayOfWeek(time) as &quot;星期&quot;,</span><br><span class="line">	toDate(time, &#x27;Asia/Shanghai&#x27;) AS date_shanghai,</span><br><span class="line">	toDateTime(time, &#x27;Asia/Shanghai&#x27;) AS time_shanghai,</span><br><span class="line">	-- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻</span><br><span class="line">	toStartOfYear(time),</span><br><span class="line">	toStartOfMonth(time),</span><br><span class="line">	toStartOfQuarter(time),</span><br><span class="line">	toStartOfDay(time) AS cur_start_daytime,</span><br><span class="line">	toStartOfHour(time) as cur_start_hour,</span><br><span class="line">	toStartOfMinute(time) AS cur_start_minute,</span><br><span class="line">	-- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号</span><br><span class="line">	toRelativeYearNum(time),</span><br><span class="line">	toRelativeQuarterNum(time);</span><br><span class="line">	</span><br><span class="line">SELECT</span><br><span class="line">	toDateTime(&#x27;2019-07-30 14:27:30&#x27;) as time,</span><br><span class="line">	toISOYear(time) AS iso_year,</span><br><span class="line">	toISOWeek(time) AS iso_week,</span><br><span class="line">	now() AS cur_dateTime1, -- 返回当前时间yyyy-MM-dd HH:mm:ss</span><br><span class="line">	today() AS cur_dateTime2, -- 其功能与&#x27;toDate(now())&#x27;相同</span><br><span class="line">	yesterday() AS yesterday, -- 当前日期的上一天</span><br><span class="line">	-- timeSlot(1) AS timeSlot_1, -- 出现异常！！将时间向前取整半小时</span><br><span class="line">	toDate(time) as getY_M_d;</span><br><span class="line">-- 目前只有这三种格式，没有什么toYYYY(),toYYYddmm()之类的函数，不要想当然。</span><br><span class="line">SELECT</span><br><span class="line">	now() as nowTime,</span><br><span class="line">	-- 将Date或DateTime转换为包含年份和月份编号的UInt32类型的数字（YYYY * 100 + MM）</span><br><span class="line">	toYYYYMMDDhhmmss(nowTime),</span><br><span class="line">	toYYYYMMDD(nowTime),</span><br><span class="line">	toYYYYMM(nowTime);</span><br><span class="line">-- formatDateTime(Time, Format[,Timezone])函数引用</span><br><span class="line">SELECT</span><br><span class="line">	now() as now_time,</span><br><span class="line">	toDateTime(&#x27;2019-07-31 18:20:30&#x27;) AS def_datetime,</span><br><span class="line">	formatDateTime(now_time, &#x27;%D&#x27;) AS now_time_day_month_year,-- 07/30/19</span><br><span class="line">--	toDateTime(&#x27;2019-07-31 18:20:30&#x27;, &#x27;Asia/Shanghai&#x27;) AS def_datetime1, -- 指定时区</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%Y&#x27;) AS def_datetime_year, -- 2019（指定日期为2019年）</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%y&#x27;) AS def_datetime_year_litter, -- 19（指定日期为19年,Year, last two digits (00-99),本世纪的第19年）</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%H&#x27;) AS hour24, -- 18 下午六点</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%I&#x27;) AS hour12, -- 06下午六点</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%p&#x27;) AS PMorAM, -- 指定时间是上午还是下午</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%w&#x27;) AS def_datetime_get_curWeek,-- 3(指定日期为星期三)</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%F&#x27;) AS def_datetime_get_date,-- 2019-07-31 </span><br><span class="line">	formatDateTime(def_datetime, &#x27;%T&#x27;) AS def_datetime_get_time,-- 18:20:30</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%M&#x27;) AS def_datetime_get_minute,-- 20(得到指定事件的“分”，minute (00-59))</span><br><span class="line">	formatDateTime(def_datetime, &#x27;%S&#x27;) AS def_datetime_get_second;-- 30(得到指定事件的“秒”，second (00-59))</span><br><span class="line">	</span><br><span class="line"> </span><br><span class="line">-- 1.跳转到之后的日期函数</span><br><span class="line">-- 第一种，日期格式（指定日期，需注意时区的问题）</span><br><span class="line">WITH</span><br><span class="line">	toDate(&#x27;2019-09-09&#x27;) AS date,</span><br><span class="line">	toDateTime(&#x27;2019-09-09 00:00:00&#x27;) AS date_time</span><br><span class="line">SELECT</span><br><span class="line">	addYears(date, 1) AS add_years_with_date,</span><br><span class="line">	addYears(date_time, 0) AS add_years_with_date_time;</span><br><span class="line">-- 第二种，日期格式（当前，本地时间）</span><br><span class="line">WITH</span><br><span class="line">	toDate(now()) as date,</span><br><span class="line">	toDateTime(now()) as date_time</span><br><span class="line">SELECT</span><br><span class="line">	now() as now_time,-- 当前时间</span><br><span class="line">	addYears(date, 1) AS add_years_with_date,-- 之后1年</span><br><span class="line">	addYears(date_time, 1) AS add_years_with_date_time,</span><br><span class="line">	addMonths(date, 1) AS add_months_with_date,-- 之后1月</span><br><span class="line">	addMonths(date_time, 1) AS add_months_with_date_time,</span><br><span class="line">	addWeeks(date, 1) AS add_weeks_with_date,--之后1周</span><br><span class="line">	addWeeks(date_time, 1) AS add_weeks_with_date_time,</span><br><span class="line">	addDays(date, 1) AS add_days_with_date,-- 之后1天</span><br><span class="line">	addDays(date_time, 1) AS add_days_with_date_time,</span><br><span class="line">	addHours(date_time, 1) AS add_hours_with_date_time,--之后1小时</span><br><span class="line">	addMinutes(date_time, 1) AS add_minutes_with_date_time,--之后1分中</span><br><span class="line">	addSeconds(date_time, 10) AS add_seconds_with_date_time,-- 之后10秒钟</span><br><span class="line">	addQuarters(date, 1) AS add_quarters_with_date, -- 之后1个季度</span><br><span class="line">	addQuarters(date_time, 1) AS add_quarters_with_date_time;</span><br><span class="line">	</span><br><span class="line">-- 2.跳转到当前日期之前的函数(函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime)</span><br><span class="line">WITH</span><br><span class="line">	toDate(now()) as date,</span><br><span class="line">	toDateTime(now()) as date_time</span><br><span class="line">SELECT</span><br><span class="line">	subtractYears(date, 1) AS subtract_years_with_date,</span><br><span class="line">    subtractYears(date_time, 1) AS subtract_years_with_date_time,</span><br><span class="line">	subtractQuarters(date, 1) AS subtract_Quarters_with_date,</span><br><span class="line">    subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,</span><br><span class="line">	subtractMonths(date, 1) AS subtract_Months_with_date,</span><br><span class="line">    subtractMonths(date_time, 1) AS subtract_Months_with_date_time,</span><br><span class="line">	subtractWeeks(date, 1) AS subtract_Weeks_with_date,</span><br><span class="line">    subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,</span><br><span class="line">	subtractDays(date, 1) AS subtract_Days_with_date,</span><br><span class="line">    subtractDays(date_time, 1) AS subtract_Days_with_date_time,</span><br><span class="line">    subtractHours(date_time, 1) AS subtract_Hours_with_date_time,</span><br><span class="line">    subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,</span><br><span class="line">    subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;</span><br><span class="line">SELECT toDate(&#x27;2019-07-31&#x27;, &#x27;Asia/GuangZhou&#x27;) as date_guangzhou;</span><br><span class="line">SELECT toDate(&#x27;2019-07-31&#x27;), toDate(&#x27;2019-07-31&#x27;, &#x27;Asia/Beijing&#x27;) as date_beijing;</span><br><span class="line">-- 亚洲只能加载上海的timezone？？？</span><br><span class="line">SELECT toDateTime(&#x27;2019-07-31 10:10:10&#x27;, &#x27;Asia/Shanghai&#x27;) as date_shanghai;</span><br><span class="line"> </span><br><span class="line">-- 计算连个时刻在不同时间单位下的差值</span><br><span class="line">-- 第一种：指定时间计算差值示例</span><br><span class="line">WITH</span><br><span class="line">	toDateTime(&#x27;2019-07-30 10:10:10&#x27;, &#x27;Asia/Shanghai&#x27;) as date_shanghai_one,</span><br><span class="line">	toDateTime(&#x27;2020-10-31 11:20:30&#x27;, &#x27;Asia/Shanghai&#x27;) as date_shanghai_two</span><br><span class="line">SELECT</span><br><span class="line">	dateDiff(&#x27;year&#x27;, date_shanghai_one, date_shanghai_two) as diff_years,</span><br><span class="line">	dateDiff(&#x27;month&#x27;, date_shanghai_one, date_shanghai_two) as diff_months,</span><br><span class="line">	dateDiff(&#x27;week&#x27;, date_shanghai_one, date_shanghai_two) as diff_week,</span><br><span class="line">	dateDiff(&#x27;day&#x27;, date_shanghai_one, date_shanghai_two) as diff_days,</span><br><span class="line">	dateDiff(&#x27;hour&#x27;, date_shanghai_one, date_shanghai_two) as diff_hours,</span><br><span class="line">	dateDiff(&#x27;minute&#x27;, date_shanghai_one, date_shanghai_two) as diff_minutes,</span><br><span class="line">	dateDiff(&#x27;second&#x27;, date_shanghai_one, date_shanghai_two) as diff_seconds;</span><br><span class="line">-- 第二种：本地当前时间示例</span><br><span class="line">WITH</span><br><span class="line">	now() as date_time</span><br><span class="line">SELECT</span><br><span class="line">	dateDiff(&#x27;year&#x27;, date_time, addYears(date_time, 1)) as diff_years,</span><br><span class="line">	dateDiff(&#x27;month&#x27;, date_time, addMonths(date_time, 2)) as diff_months,</span><br><span class="line">	dateDiff(&#x27;week&#x27;, date_time, addWeeks(date_time, 3)) as diff_week,</span><br><span class="line">	dateDiff(&#x27;day&#x27;, date_time, addDays(date_time, 3)) as diff_days,</span><br><span class="line">	dateDiff(&#x27;hour&#x27;, date_time, addHours(date_time, 3)) as diff_hours,</span><br><span class="line">	dateDiff(&#x27;minute&#x27;, date_time, addMinutes(date_time, 30)) as diff_minutes,</span><br><span class="line">	dateDiff(&#x27;second&#x27;, date_time, addSeconds(date_time, 35)) as diff_seconds;</span><br><span class="line">	</span><br><span class="line">-- timeSlot(StartTime, Duration, [,Size])</span><br><span class="line">-- 它返回一个时间数组，其中包括从从“StartTime”开始到“StartTime + Duration 秒”内的所有符合“size”（以秒为单位）步长的时间点</span><br><span class="line">-- 作用:搜索在相应会话中综合浏览量是非常有用的。</span><br><span class="line">SELECT </span><br><span class="line">	timeSlots(toDateTime(&#x27;2012-01-01 12:20:00&#x27;), toUInt32(600)) as dateTimeArray,</span><br><span class="line">	dateTimeArray[0] as arr_index_0, -- no result.</span><br><span class="line">	dateTimeArray[1] as arr_index_1, -- 2012-01-01 20:00:00</span><br><span class="line">	dateTimeArray[2] as arr_index_2, -- 2012-01-01 20:30:00</span><br><span class="line">	dateTimeArray[3] as arr_index_3, -- no result.</span><br><span class="line">	dateTimeArray[4] as arr_index_4; -- no result.</span><br><span class="line">-- toUInt32(600) 表示之后间距20秒的时刻</span><br><span class="line">SELECT </span><br><span class="line">	timeSlots(now(), toUInt32(600), 20) as dateTimeArray, -- 类似于：引用地址</span><br><span class="line">	dateTimeArray[0] as arr_index_0, -- no result.为什么？</span><br><span class="line">	dateTimeArray[1] as arr_index_1,</span><br><span class="line">	dateTimeArray[2] as arr_index_2,</span><br><span class="line">	dateTimeArray[3] as arr_index_3,</span><br><span class="line">	dateTimeArray[4] as arr_index_4,</span><br><span class="line">	dateTimeArray[5] as arr_index_5;</span><br><span class="line">-- 指定时间为基准，之后每个元素增加20秒</span><br><span class="line">SELECT</span><br><span class="line">	timeSlots(toDateTime(&#x27;2012-01-01 12:20:00&#x27;), toUInt32(600), 20) as cur_dateTimeArray, -- 类似于：引用地址</span><br><span class="line">	cur_dateTimeArray[0] as arr_index_0, -- no result.为什么？</span><br><span class="line">	cur_dateTimeArray[1] as arr_index_1, -- 2012-01-01 20:20:00</span><br><span class="line">	cur_dateTimeArray[2] as arr_index_2, -- 2012-01-01 20:20:20</span><br><span class="line">	cur_dateTimeArray[3] as arr_index_3, -- 2012-01-01 20:20:40</span><br><span class="line">	cur_dateTimeArray[4] as arr_index_4, -- 2012-01-01 20:21:00</span><br><span class="line">	cur_dateTimeArray[5] as arr_index_5; -- 2012-01-01 20:21:20</span><br><span class="line"> </span><br><span class="line">-- 六、字符串函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 字符串函数：</span><br><span class="line">SELECT</span><br><span class="line">	length(&#x27;hello world&#x27;) as str_length, -- 按照Unicode编码计算长度“你好”的长度为6</span><br><span class="line">	empty(&#x27;hello world&#x27;),-- 判断字符串是否为空，空为1，非空为0</span><br><span class="line">	notEmpty(&#x27;hello world&#x27;),</span><br><span class="line">	lengthUTF8(&#x27;hello world&#x27;), -- 按照实际字符计算长度“你好”为2</span><br><span class="line">	char_length(&#x27;hello world&#x27;), -- 同 lengthUTF8()</span><br><span class="line">	character_length(&#x27;hello world&#x27;), -- 同 lengthUTF8(),</span><br><span class="line">	lower(&#x27;abcd123--&#x27;),--字母全部小写（将字符串中的ASCII转换为小写。）</span><br><span class="line">	upper(&#x27;abcd123--&#x27;),--字母全部大写（将字符串中的ASCII转换为大写。）</span><br><span class="line">	lowerUTF8(&#x27;abcd123-/*\8asd-\\&#x27;), -- abcd123-/*8asd-\</span><br><span class="line">	upperUTF8(&#x27;abcd123--&#x27;), -- ABCD123--</span><br><span class="line">	isValidUTF8(&#x27;abcd123--/*\*&#x27;); --检查字符串是否为有效的UTF-8编码，是则返回1，否则返回0。</span><br><span class="line">SELECT notEmpty(&#x27;&#x27;), notEmpty(NULL), notEmpty(&#x27;he&#x27;); -- 0,空,1</span><br><span class="line">SELECT toValidUTF8(&#x27;\x61\xF0\x80\x80\x80b&#x27;);</span><br><span class="line">-- reverseUTF8():以Unicode字符为单位反转UTF-8编码的字符串。如果字符串不是UTF-8编码，则可能获取到一个非预期的结果（不会抛出异常）</span><br><span class="line">SELECT reverse(&#x27;abcdefg&#x27;), reverseUTF8(&#x27;abcdefg&#x27;);</span><br><span class="line">-- 2.字符串维度自定义安排</span><br><span class="line">SELECT format(&#x27;&#123;1&#125; &#123;0&#125; &#123;1&#125;&#x27;, &#x27;World&#x27;, &#x27;Hello&#x27;); -- 输出：Hello World Hello</span><br><span class="line">SELECT format(&#x27;&#123;0&#125; &#123;0&#125; &#123;1&#125; &#123;1&#125;&#x27;, &#x27;one&#x27;, &#x27;two&#x27;); -- 输出：one one two two</span><br><span class="line">SELECT format(&#x27;&#123;&#125; &#123;&#125;&#x27;, &#x27;Hello&#x27;, &#x27;World&#x27;); -- 输出：Hello World</span><br><span class="line">-- 3.字符串拼接 concat(s1,s2,s3,...)</span><br><span class="line">SELECT concat(&#x27;Hello&#x27;,&#x27; &#x27;,&#x27;World&#x27;, &#x27;!&#x27;);-- Hello World!</span><br><span class="line">-- 与concat相同，区别在于，你需要保证concat(s1, s2, s3) -&gt; s4是单射的，它将用于GROUP BY的优化。</span><br><span class="line">SELECT concatAssumeInjective(&#x27;Hello&#x27;,&#x27; &#x27;,&#x27;World&#x27;, &#x27;!&#x27;);-- Hello World!</span><br><span class="line">-- 4.字符串截取：substring(s, offset, length), mid(s, offset, length), substr(s, offset, length)</span><br><span class="line">-- 以字节为单位截取指定位置字符串，返回以‘offset’位置为开头，长度为‘length’的子串。‘offset’从1开始（与标准SQL相同）。‘offset’和‘length’参数必须是常量。</span><br><span class="line">SELECT </span><br><span class="line">	substring(&#x27;abcdefg&#x27;, 1, 3),-- abc</span><br><span class="line">	substring(&#x27;你好，世界&#x27;, 1, 3),-- 你</span><br><span class="line">	substringUTF8(&#x27;你好，世界&#x27;, 1, 3); -- 你好，</span><br><span class="line">-- 5.字符串拼接：appendTrailingCharIfAbsent(s, c)</span><br><span class="line">-- 如果‘s’字符串非空并且末尾不包含‘c’字符，则将‘c’字符附加到末尾。</span><br><span class="line">SELECT </span><br><span class="line">	appendTrailingCharIfAbsent(&#x27;good&#x27;,&#x27;c&#x27;), -- goodc</span><br><span class="line">	appendTrailingCharIfAbsent(&#x27;goodccc&#x27;,&#x27;c&#x27;); -- goodccc</span><br><span class="line">-- 6.字符串编码转换：convertCharset(s, from, to) 返回从‘from’中的编码转换为‘to’中的编码的字符串‘s’。</span><br><span class="line">SELECT </span><br><span class="line">	convertCharset(&#x27;hello&#x27;, &#x27;UTF8&#x27;,&#x27;Unicode&#x27;),-- ��h</span><br><span class="line">	convertCharset(&#x27;hello&#x27;, &#x27;Unicode&#x27;, &#x27;UTF8&#x27;),-- 桥汬�</span><br><span class="line">	convertCharset(&#x27;hello&#x27;, &#x27;Unicode&#x27;, &#x27;ASCII&#x27;),-- </span><br><span class="line">	convertCharset(&#x27;hello&#x27;, &#x27;ascii&#x27;, &#x27;ascii&#x27;),--hello</span><br><span class="line">	convertCharset(&#x27;hello&#x27;, &#x27;UTF8&#x27;,&#x27;UTF8&#x27;);-- hello</span><br><span class="line">SELECT </span><br><span class="line">	base64Encode(&#x27;username+password&#x27;),-- dXNlcm5hbWUrcGFzc3dvcmQ=</span><br><span class="line">	base64Decode(&#x27;dXNlcm5hbWUrcGFzc3dvcmQ=&#x27;), -- username+password</span><br><span class="line">	-- 使用base64将字符串解码成原始字符串。但如果出现错误，将返回空字符串。</span><br><span class="line">	tryBase64Decode(&#x27;dXNlcm5hbWUrcGFzc3dvcmQ=&#x27;);</span><br><span class="line">-- 7.判断字符串是否已什么结尾或结束，返回1：true，0：flase</span><br><span class="line">-- endsWith(s, suffix)  返回是否以指定的后缀结尾。如果字符串以指定的后缀结束，则返回1，否则返回0</span><br><span class="line">-- startWith(s, prefix) 返回是否以指定的前缀开头。如果字符串以指定的前缀开头，则返回1，否则返回0。</span><br><span class="line">SELECT</span><br><span class="line">	endsWith(&#x27;string&#x27;,&#x27;g&#x27;),</span><br><span class="line">	startsWith(&#x27;string&#x27;, &#x27;str&#x27;); -- 1  true</span><br><span class="line">-- 8.删除左侧空白字符</span><br><span class="line">-- trimLeft(s)  返回一个字符串，用于删除左侧的空白字符</span><br><span class="line">-- trimRight(s) 返回一个字符串，用于删除右侧的空白字符</span><br><span class="line">-- trimBoth(s)  返回一个字符串，用于删除左侧和右侧的空白字符</span><br><span class="line">SELECT</span><br><span class="line">	trimLeft(&#x27;  sdfdgs&#x27;), -- sdfdgs</span><br><span class="line">	trimRight(&#x27;abcd   &#x27;), -- abcd</span><br><span class="line">	trimBoth(&#x27;  abcd  &#x27;); -- abcd</span><br><span class="line">-- 七、字符串搜索函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 字符串搜索函数</span><br><span class="line">-- pasition(haystack, needle), 显示needle在haystack的第一个出现的位置。</span><br><span class="line">SELECT </span><br><span class="line">	POSITION(&#x27;2121stringstrstrstrstr&#x27;,&#x27;str&#x27;) AS positionSearch, -- 5</span><br><span class="line">	POSITION(&#x27;你好，hello,12323-你好，你，好sdfd*dg&#x27;, &#x27;你，好&#x27;),-- 31</span><br><span class="line">	positionUTF8(&#x27;n12你好&#x27;,&#x27;你好&#x27;) AS positionUTF8,-- 4</span><br><span class="line">	positionCaseInsensitive(&#x27;ABCDCDEFABCD&#x27;,&#x27;bc&#x27;) AS positionCaseInsensitive, --2</span><br><span class="line">	locate(&#x27;hellohellohellohello&#x27;,&#x27;ello&#x27;); -- 2</span><br><span class="line">-- multiSearchAllPositions(haystack, [needle1, needle2, ..., needlen])</span><br><span class="line">-- 注意：在所有multiSearch*函数中，由于实现规范，needles的数量应小于2^8。</span><br><span class="line">-- 函数返回一个数组，其中包含所有匹配needlei的位置</span><br><span class="line">SELECT</span><br><span class="line">	multiSearchAllPositions(&#x27;goodnamegoodnamegoodhellohihihi&#x27;, [&#x27;dn&#x27;, &#x27;good&#x27;]) as multiSearch,-- [4,1]</span><br><span class="line">	multiSearchAllPositionsCaseInsensitive(&#x27;nameSsdfagpSSDFDFetgfderef&#x27;, [&#x27;SS&#x27;,&#x27;fa&#x27;]) as multiCaseInsensitive,</span><br><span class="line">	multiSearchAllPositionsUTF8(&#x27;nameSsdfazz轴功率gpSSDFDFetgfderef&#x27;, [&#x27;Ss&#x27;,&#x27;fa&#x27;, &#x27;zz轴&#x27;]) AS multiSearchUTF8,</span><br><span class="line">	multiSearchAllPositionsCaseInsensitiveUTF8(&#x27;nameSsdfazz轴功率gpSSDFDFetgfderef&#x27;, [&#x27;Ss&#x27;,&#x27;fa&#x27;, &#x27;zz轴&#x27;]) AS multiCaseInsensitiveUTF8;</span><br><span class="line">-- 检查字符串是否与pattern正则表达式匹配。pattern可以是一个任意的re2正则表达式。 re2正则表达式的语法比Perl正则表达式的语法存在更多限制。</span><br><span class="line">-- match(haystack, pattern) 匹配到了则返回1，否则返回0</span><br><span class="line">SELECT</span><br><span class="line">	match(&#x27;1232434sadgaDDFSrefds&#x27;, &#x27;[0-9a-zA-Z]&#x27;), -- 存在匹配的字符，返回1</span><br><span class="line">	match(&#x27;1232321&#x27;, &#x27;[a-z]&#x27;); -- 不存在匹配的字符，返回0</span><br><span class="line">-- 与match相同，但如果所有正则表达式都不匹配，则返回0；如果任何模式匹配，则返回1。它使用hyperscan库。对于在字符串中搜索子字符串的模式，最好使用“multisearchany”，因为它更高效。</span><br><span class="line">-- multiMatchAny(haystack, [pattern1, pattern2, ..., patternn]) </span><br><span class="line">-- 注意：任何haystack字符串的长度必须小于232字节，否则抛出异常。这种限制是因为hyperscan API而产生的。</span><br><span class="line">-- 多个正则表达式对原始字符进行匹配，如若只有一个正则表达式匹配上了则返回1，否则返回0</span><br><span class="line">SELECT </span><br><span class="line">	multiMatchAny(&#x27;abcABC&#x27;,[&#x27;[0-9]&#x27;,&#x27;[a-zA-Z]&#x27;]) AS multiMatchAnyOne, -- 1</span><br><span class="line">	multiMatchAny(&#x27;123abcABC&#x27;,[&#x27;[0-9]&#x27;,&#x27;[a-zA-Z]&#x27;]) AS multiMatchAnyTwo, --1</span><br><span class="line">	-- 与multiMatchAny相同，但返回与haystack匹配的任何内容的索引位置。</span><br><span class="line">	multiMatchAnyIndex(&#x27;123abcABC&#x27;, [&#x27;[0-9]&#x27;,&#x27;[a-zA-Z]&#x27;]) as multiMatchAnyIndex; --2</span><br><span class="line">-- 模糊匹配：like()函数，注意大写敏感。</span><br><span class="line">-- % 表示任何字节数（包括零字符）</span><br><span class="line">-- _ 表示任何一个字节</span><br><span class="line">SELECT</span><br><span class="line">	&#x27;hello&#x27; LIKE &#x27;%h%&#x27; as LIKE_UP, -- 1</span><br><span class="line">	&#x27;hello&#x27; like &#x27;he&#x27; AS like_low, -- 0</span><br><span class="line">	&#x27;hello&#x27; not like &#x27;he&#x27; AS not_like, -- 1</span><br><span class="line">	&#x27;hello&#x27; like &#x27;%he%&#x27; AS like_litter, -- 1</span><br><span class="line">	like(&#x27;adgadgadfa1232&#x27;, &#x27;_12_&#x27;) AS like_func,</span><br><span class="line">	like(&#x27;sdfasdfasd&#x27;, &#x27;[a-z]&#x27;) AS like_func2, -- 0</span><br><span class="line">	notLike(&#x27;1232423&#x27;, &#x27;[a-zA-Z]&#x27;) AS not_like_func; -- 1</span><br><span class="line">-- 使用字符串截取字符串：extract(haystack, pattern)</span><br><span class="line">-- 使用正则表达式截取字符串。如果‘haystack’与‘pattern’不匹配，则返回空字符串。如果正则表达式中不包含子模式，它将获取与整个正则表达式匹配的子串。否则，它将获取与第一个子模式匹配的子串。</span><br><span class="line">SELECT</span><br><span class="line">	extractAll(&#x27;hellogoodaimantIdeaIDEAfasd123232&#x27;, &#x27;[0-9]&#x27;), -- [&#x27;1&#x27;,&#x27;2&#x27;,&#x27;3&#x27;,&#x27;2&#x27;,&#x27;3&#x27;,&#x27;2&#x27;]</span><br><span class="line">	extractAll(&#x27;12323dSDFRE&#x27;, &#x27;[A-Z]&#x27;),-- [&#x27;S&#x27;,&#x27;D&#x27;,&#x27;F&#x27;,&#x27;R&#x27;,&#x27;E&#x27;]</span><br><span class="line">	extract(&#x27;helloclickhouse&#x27;, &#x27;[a-z]&#x27;);-- h</span><br><span class="line">-- ngramSearch(haystack, needle)</span><br><span class="line">-- 基于4-gram计算haystack和needle之间的距离：计算两个4-gram集合之间的对称差异，并用它们的基数和对其进行归一化。</span><br><span class="line">-- 返回0到1之间的任何浮点数 -- 越接近0则表示越多的字符串彼此相似。</span><br><span class="line">-- 如果常量的needle或haystack超过32KB，函数将抛出异常。如果非常量的haystack或needle字符串超过32Kb，则距离始终为1。</span><br><span class="line">SELECT </span><br><span class="line">	ngramDistance(&#x27;hello123456789&#x27;,&#x27;123&#x27;) AS ngramDistance, </span><br><span class="line">	ngramDistanceCaseInsensitive(&#x27;hello123456789&#x27;,&#x27;123&#x27;) AS ngramDistanceCaseInsensitive,</span><br><span class="line">	ngramDistanceUTF8(&#x27;hello123456789&#x27;,&#x27;123&#x27;) AS ngramDistanceUTF8,</span><br><span class="line">	ngramDistanceCaseInsensitiveUTF8(&#x27;hello123456789&#x27;,&#x27;123&#x27;) AS ngramDistanceCaseInsensitiveUTF8;</span><br><span class="line">-- 注意：对于UTF-8，我们使用3-gram。所有这些都不是完全公平的n-gram距离。</span><br><span class="line">-- 我们使用2字节哈希来散列n-gram，然后计算这些哈希表之间的（非）对称差异 - 可能会发生冲突。</span><br><span class="line">-- 对于UTF-8不区分大小写的格式，我们不使用公平的tolower函数 </span><br><span class="line">-- 我们将每个Unicode字符字节的第5位（从零开始）和字节的第一位归零 </span><br><span class="line">-- 这适用于拉丁语，主要用于所有西里尔字母。</span><br><span class="line">--八、字符串替换函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 字符串替换函数</span><br><span class="line">-- 替换匹配到的字符串</span><br><span class="line">-- replaceOne(haystack, pattern, replacement)</span><br><span class="line">-- 用‘replacement’子串替换‘haystack’中与‘pattern’子串第一个匹配的匹配项（如果存在）。 ‘pattern’和‘replacement’必须是常量。</span><br><span class="line">-- replaceAll(haystack, pattern, replacement), replace(haystack, pattern, replacement)</span><br><span class="line">-- 用‘replacement’子串替换‘haystack’中出现的所有‘pattern’子串。</span><br><span class="line">SELECT </span><br><span class="line">	replaceOne(&#x27;hed1234544&#x27;, &#x27;4&#x27;, &#x27;*&#x27;) AS replaceOne,-- hed123*544</span><br><span class="line">	replaceRegexpOne(&#x27;hed1234544&#x27;, &#x27;4&#x27;, &#x27;*&#x27;) AS replaceRegexpOne,-- hed123*544</span><br><span class="line">	replace(&#x27;hed1234544&#x27;, &#x27;4&#x27;, &#x27;*&#x27;) AS replace, -- hed123*5**</span><br><span class="line">	replaceAll(&#x27;hed1234544&#x27;, &#x27;4&#x27;, &#x27;*&#x27;) AS replaceAll;-- hed123*5**</span><br><span class="line">-- 实例：2019-07-31 改变成  07/31/2019</span><br><span class="line">SELECT</span><br><span class="line">	toDate(now()) AS now_date,</span><br><span class="line">	replaceRegexpOne(toString(now_date), &#x27;(\\d&#123;4&#125;)-(\\d&#123;2&#125;)-(\\d&#123;2&#125;)&#x27;, &#x27;\\2/\\3/\\1&#x27;) AS format_date;</span><br><span class="line">-- 示例：赋值字符串10次</span><br><span class="line">SELECT replaceRegexpOne(&#x27;Hello, World!&#x27;, &#x27;.*&#x27;, &#x27;\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0&#x27;) AS res;</span><br><span class="line">-- replaceRegexpAll(haystack, pattern, replacement)</span><br><span class="line">-- 与replaceRegexpOne相同，但会替换所有出现的匹配项。例如：</span><br><span class="line">SELECT replaceRegexpAll(&#x27;hello,world!&#x27;, &#x27;.&#x27;, &#x27;\\0\\0&#x27;) as res; -- hheelllloo,,wwoorrlldd!!</span><br><span class="line">SELECT replaceRegexpAll(&#x27;hello o o, world.&#x27;, &#x27; &#x27;, &#x27;*&#x27;) as res; -- hello*o*o,*world.</span><br><span class="line">-- 函数：regexpQuoteMeta(s)   该函数用于在字符串中的某些预定义字符之前添加反斜杠。</span><br><span class="line">-- 预定义字符：&#x27;0&#x27;，&#x27;\&#x27;，&#x27;|&#x27;，&#x27;(&#x27;，&#x27;)&#x27;，&#x27;^&#x27;，&#x27;$&#x27;，&#x27;。&#x27;，&#x27;[&#x27;，&#x27;]&#x27;，&#x27;？&#x27;，&#x27;* &#x27;，&#x27;+&#x27;，&#x27;&#123;&#x27;，&#x27;：&#x27;，&#x27; - &#x27;。 </span><br><span class="line">-- 这个实现与re2 :: RE2 :: QuoteMeta略有不同。它以\0而不是\x00转义零字节，它只转义所需的字符</span><br><span class="line">---- 简言之，就是不处理转义字符，一般如果没有用的这个函数，都会有转义的情况出现。</span><br><span class="line">SELECT regexpQuoteMeta(&#x27;\\\\|[]&#123;&#125;+_-=@!~`&amp;^*%$#&#x27;); -- \\\\\|\[\]\&#123;&#125;\+_\-=@!~`&amp;\^\*%\$#</span><br><span class="line">SELECT toString(&#x27;\\\\&#x27;); -- \\</span><br><span class="line"> </span><br><span class="line">--九、条件函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 条件函数</span><br><span class="line">-- 1. if(cond, then, else)函数：类似于三元操作符。</span><br><span class="line">-- 中文字符使用双引号，英文字符可不使用引号也可使用当引号或双引号，根据具体情况而定。</span><br><span class="line">-- 如果cond ！= 0则返回then，如果cond = 0则返回else。 cond必须是UInt8类型，then和else必须存在最低的共同类型。</span><br><span class="line">-- 注意：then和else可以是NULL</span><br><span class="line">SELECT </span><br><span class="line">&gt; 10 ? &#x27;desc&#x27; : &#x27;asc&#x27; AS &quot;三元操作符&quot;,</span><br><span class="line">	if(12 &gt; 10, &#x27;desc&#x27; , &#x27;asc&#x27;) AS &quot;if()函数&quot;,</span><br><span class="line">	if(12 &gt; 10, NULL, NULL);</span><br><span class="line">-- 2. multiIf(cond_1, then_1, cond_2, then_2...else)</span><br><span class="line">-- 允许您在查询中更紧凑地编写CASE运算符。类似于java中的switch语法（可以接受2n+1个参数）</span><br><span class="line">SELECT multiIf(1,&#x27;one&#x27;,2,&#x27;two&#x27;,3,&#x27;three&#x27;,&#x27;not this index&#x27;);-- 关联case条件表达式</span><br><span class="line">--十、数学函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 数学函数</span><br><span class="line">SELECT</span><br><span class="line">* e() AS E,</span><br><span class="line">* pi() AS PI,</span><br><span class="line">	sqrt(25) AS sqrt_25, --接受一个数值类型的参数并返回它的平方根。</span><br><span class="line">	cbrt(27) AS cbrt_27, --接受一个数值类型的参数并返回它的立方根。</span><br><span class="line">	exp(10), --接受一个数值类型的参数并返回它的指数</span><br><span class="line">	exp10(10), --接受一个数值类型的参数并返回它的10的x次幂。</span><br><span class="line">	log(10) AS LOG,</span><br><span class="line">	log2(10) AS LOG2, --接受一个数值类型的参数并返回它的底2对数。</span><br><span class="line">	ln(e()) AS LOG10; --接受一个数值类型的参数并返回它的自然对数</span><br><span class="line">-- 示例：三西格玛准则</span><br><span class="line">SELECT erf(3 / sqrt(2)); -- 0.997</span><br><span class="line">SELECT </span><br><span class="line">	sin(90), -- 返回x的三角正弦值。</span><br><span class="line">	cos(90), -- 返回x的三角余弦值。</span><br><span class="line">	tan(90), -- 返回x的三角正切值</span><br><span class="line">	acos(0), -- 返回x的反三角余弦值。</span><br><span class="line">	asin(1), -- 返回x的反三角正弦值。</span><br><span class="line">	atan(45); -- 返回x的反三角正切值。</span><br><span class="line">-- pow(x, y), power(x, y) 接受x和y两个参数。返回x的y次方。</span><br><span class="line">SELECT</span><br><span class="line">	pow(2, 3), -- 2的三次方</span><br><span class="line">	pow(3, 2); -- 3的平方</span><br><span class="line">SELECT</span><br><span class="line">	intExp2(4), --2^4 	接受一个数值类型的参数并返回它的2的x次幂（UInt64）。</span><br><span class="line">	intExp10(2);--10^2	接受一个数值类型的参数并返回它的10的x次幂（UInt64）。</span><br><span class="line">	</span><br><span class="line">-- 十一、取整函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 取整函数</span><br><span class="line">-- 1.向下取整：floor(x[,N])</span><br><span class="line">SELECT </span><br><span class="line">	floor(toFloat32(12.08098), 2), -- 12.08</span><br><span class="line">	floor(toFloat32(12.2323), 2), -- 12.23</span><br><span class="line">	floor(toFloat32(12.89788), -1), -- 10</span><br><span class="line">	floor(toFloat32(12.09590), 3), -- 12.095 (注意：如果按照正常的四舍五入，则应该是12.096，为什么呢？)</span><br><span class="line">	floor(toFloat32(12.0987), 3),-- 12.098</span><br><span class="line">	floor(10, 2); -- 10</span><br><span class="line">-- 2.四舍五入：round(expression [, decimal_places])</span><br><span class="line">--	如果decimal_places=0,则取整数；</span><br><span class="line">--	如果&gt;0,则将值舍入小数点右侧；</span><br><span class="line">--	如果&lt;0,则将小数点左侧的值四舍五入。</span><br><span class="line">SELECT</span><br><span class="line">	round(toFloat32(12.1234), 3),</span><br><span class="line">	round(toFloat32(12.0025), 3), -- 12.002(注意：为什么不是12.003呢？)</span><br><span class="line">	-- round函数只会最多保留三位有效数字</span><br><span class="line">	round(toFloat32(12.0025), 4), -- 12.002</span><br><span class="line">	round(toFloat32(12.0025002323), 100); -- 12.003</span><br><span class="line">-- 示例：</span><br><span class="line">SELECT </span><br><span class="line">	round(toFloat32(10 / 3)), -- 3  </span><br><span class="line">	round(toFloat32(10 / 3), 2), -- 3.33</span><br><span class="line">	round(toFloat32(10.000/3), 3), -- 3.333</span><br><span class="line">	round(toFloat32(10.000/3), 6); -- 3.333</span><br><span class="line">-- roundToExp2() 接受一个数字。如果数字小于1，则返回0。否则，它将数字向下舍入到最接近的（整个非负）2的x次幂。</span><br><span class="line">SELECT</span><br><span class="line">	roundToExp2(12.0129), -- 8 = 2^3</span><br><span class="line">	roundToExp2(toFloat32(0.01)); -- 0.008</span><br><span class="line">-- 3.向上取整：ceil(x[, N]) 或者 ceiling(x[, N])</span><br><span class="line">SELECT</span><br><span class="line">	ceil(12.34343, 3), -- 12.344</span><br><span class="line">	ceil(toFloat64(12.34343), 3), -- 12.344</span><br><span class="line">	ceil(toFloat32(12.34343), 3), -- 12.344</span><br><span class="line">	ceil(12.0011, 3); -- 12.002</span><br><span class="line">	</span><br><span class="line">	</span><br><span class="line">---十二、数组函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 数组函数</span><br><span class="line">-- 1.数组非空判断相关函数（真为1，假为0）</span><br><span class="line">SELECT empty([]), empty([1,2,3]), notEmpty([1,2,3]), notEmpty([]);</span><br><span class="line">-- 2.数组长度 length()  返回数组中的元素个数。 结果类型是UInt64。 该函数也适用于字符串。</span><br><span class="line">SELECT</span><br><span class="line">--	length(), -- 出现异常</span><br><span class="line">--	length([true, false]), -- 异常</span><br><span class="line">--	length([1,2,,4]), --出现异常！</span><br><span class="line">	length([]), -- 0</span><br><span class="line">	length([&#x27;a&#x27;,&#x27;b&#x27;,&#x27;c&#x27;]), -- 3</span><br><span class="line">	length([1,2,3]); -- 3</span><br><span class="line">-- 3.扩展判断非空的部分函数如下：不接受任何参数并返回适当类型的空数组</span><br><span class="line">SELECT</span><br><span class="line">	emptyArrayUInt8(), -- UInt8的空数组</span><br><span class="line">	emptyArrayUInt16(),</span><br><span class="line">	emptyArrayUInt32(),</span><br><span class="line">	emptyArrayUInt64(),</span><br><span class="line">	emptyArrayDate(),</span><br><span class="line">	emptyArrayDateTime(),</span><br><span class="line">	emptyArrayInt8(),</span><br><span class="line">	emptyArrayInt16(),</span><br><span class="line">	emptyArrayInt32(),</span><br><span class="line">	emptyArrayInt64();</span><br><span class="line">--	接受一个空数组并返回一个仅包含一个默认值元素的数组。(以下是部分示例)</span><br><span class="line">SELECT </span><br><span class="line">	emptyArrayToSingle(emptyArrayInt32()), -- 0</span><br><span class="line">	emptyArrayToSingle(emptyArrayUInt32()), -- 0</span><br><span class="line">	emptyArrayToSingle(emptyArrayDate()), -- 0002-11-30</span><br><span class="line">	emptyArrayToSingle(emptyArrayDateTime()); --0002-11-30 08:00:00</span><br><span class="line">-- 4.生成一个含有N个元素的数组,元素从0开始增长，步长尾1.</span><br><span class="line">-- range(N)  返回从0到N-1的数字数组。 以防万一，如果在数据块中创建总长度超过100,000,000个元素的数组，则抛出异常</span><br><span class="line">SELECT</span><br><span class="line">	range(10), -- [0,1,2,3,4,5,6,7,8,9]</span><br><span class="line">	range(2), -- [0,1]</span><br><span class="line">--	range(5.5), -- 出现异常，N为Int8的数据类型，正整数</span><br><span class="line">--	range(-10), -- 出现异常，DB::Exception: Illegal type Int8 of argument of function range</span><br><span class="line">	range(1); -- 0</span><br><span class="line">-- 5.新建一个数组的函数：array(x1,……) 类似于 直接[x1,……]</span><br><span class="line">-- 注意：新建数组的每个元素的数据类型需保持一致性。</span><br><span class="line">SELECT</span><br><span class="line">	array(1,2,2,3,4) AS &quot;array()函数&quot;,</span><br><span class="line">--	[1,&#x27;hello&#x27;,3], -- 出现异常，DB::Exception: There is no supertype for types UInt8, String, UInt8 because some of them are String/FixedString and some of them are not (version 19.10.1.5 (official build))</span><br><span class="line">	[1,2,3,4] AS &quot;[ ]&quot;;</span><br><span class="line">-- 6.合并N个数组  arrayConcat(arrays) 合并参数中传递的所有数组。跟java的数组差不多的合并，不会自动去重，不会自动排序</span><br><span class="line">SELECT</span><br><span class="line">	arrayConcat(array(1,2),array(2,3),array(4,5)), -- [1,2,2,3,4,5]（第一种情况）</span><br><span class="line">	arrayConcat(array(1,1),array(2,2),array(3,3)), -- [1,1,2,2,3,3]</span><br><span class="line">--	arrayConcat(array(1,2),[&#x27;a&#x27;,&#x27;c&#x27;],array(3,3)), -- 出现异常，不能将不同类型的数组进行合并</span><br><span class="line">	arrayConcat(array(1,1),[2,3],array(4,5)); -- [1,1,2,3,4,5]</span><br><span class="line">-- 7.从数组arr中获取索引为“n”的元素。</span><br><span class="line">--  n必须是任何整数类型。 数组中的索引从一开始。 支持负索引。在这种情况下，它选择从末尾开始编号的相应元素。例如，arr [-1]是数组中的最后一项。</span><br><span class="line">--  如果索引超出数组的边界，则返回默认值（数字为0，字符串为空字符串等）.</span><br><span class="line">SELECT</span><br><span class="line">	arrayElement(array(10,20,3), 1), -- 10</span><br><span class="line">	arrayElement(array(1,20,3), 2), -- 20</span><br><span class="line">	arrayElement(array(1,2,30), 3), -- 30</span><br><span class="line">	arrayElement(array(10,20,3), 0), -- 0</span><br><span class="line">	arrayElement(array(10,20,3), -3), -- 10</span><br><span class="line">	arrayElement(array(10,20,3), -2), -- 20</span><br><span class="line">	arrayElement(array(10,20,3), -1);-- 3</span><br><span class="line">-- 8.检查在数组中是否含有此元素。has(arr, elem)  包含此元素则返回1，否则返回0</span><br><span class="line">-- has()  检查&#x27;arr&#x27;数组是否具有&#x27;elem&#x27;元素。 如果元素不在数组中，则返回0;如果在，则返回1。</span><br><span class="line">-- hasAny(arr1, arr2) 返回1表示arr1和arr2存在交集。否则返回0. </span><br><span class="line">--注意：特殊的定义：</span><br><span class="line">--		① “NULL”作为数组中的元素值进行处理。</span><br><span class="line">--		② 忽略两个数组中的元素值的顺序</span><br><span class="line">-- hasAll(set, subset)  检查一个数组是否是另一个数组的子集。返回1，表示set包含subset中所有的元素</span><br><span class="line">--    set – 具有一组元素的任何类型的数组。</span><br><span class="line">--    subset – 任何类型的数组，其元素应该被测试为set的子集。</span><br><span class="line">-- 注意：特殊的定义：</span><br><span class="line">--		① 空数组是任何数组的子集。</span><br><span class="line">--		② “NULL”作为数组中的元素值进行处理。</span><br><span class="line">--		③ 忽略两个数组中的元素值的顺序。</span><br><span class="line">SELECT</span><br><span class="line">	has([1,2,3], 2), -- 1</span><br><span class="line">	has(array(1,2,3),2), -- 1</span><br><span class="line">	has([1,2,NULL], NULL), -- 1 (注意：null值的处理)</span><br><span class="line">--	has([], 2), -- 出现异常，DB::Exception: Types of array and 2nd argument of function has must be identical up to nullability or numeric types or Enum and numeric type. Passed: Array(Nothing) and UInt8</span><br><span class="line">	has([1,2], 3); -- 0</span><br><span class="line">SELECT</span><br><span class="line">	hasAll([], []), -- 1</span><br><span class="line">	hasAll([1,NULL,NULL], [NULL]), -- 1</span><br><span class="line">	hasAll([1,2,3], [1,2]), -- 1</span><br><span class="line">	hasAll([1,2,2,3], [2]), -- 1</span><br><span class="line">	hasAll(array(1,2,2,3), [2]), -- 1</span><br><span class="line">	hasAll([1,2,3], [4,5]); -- 0</span><br><span class="line">-- 多重数组（如下的二维数组）。</span><br><span class="line">SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]]); -- 0</span><br><span class="line">SELECT</span><br><span class="line">	hasAny(array(1,2,3), array(1)), -- 1</span><br><span class="line">	hasAny(array(1,2,3), array(1,4,56,80)), -- 1</span><br><span class="line">	-- []与array()是一样的含义，本质上是一直的。只不过[]更加简便而已。</span><br><span class="line">	hasAny(array(), array()), -- 0</span><br><span class="line">	hasAny([],[]), -- 0</span><br><span class="line">	hasAny([1],[]), -- 0</span><br><span class="line">	-- 空数组跟null不是一样的对象</span><br><span class="line">	hasAny([1,NULL],[]), -- 0</span><br><span class="line">	hasAny([1,NULL],[NULL,2]); -- 1</span><br><span class="line">	</span><br><span class="line">-- 9.返回数组指定元素的索引</span><br><span class="line">-- indexOf(arr, x) 返回数组中第一个‘x’元素的索引（从1开始），如果‘x’元素不存在在数组中，则返回0。</span><br><span class="line">SELECT indexOf([&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;], &#x27;one&#x27;); -- 1</span><br><span class="line">SELECT indexOf([1, 2, 4], 4); -- 3</span><br><span class="line">SELECT </span><br><span class="line">	indexOf([&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;], &#x27;one&#x27;), -- 1</span><br><span class="line">	indexOf([&#x27;one&#x27;,NULL,NULL], NULL),-- 1返回第一个找到的元素的索引位置</span><br><span class="line">	indexOf([1, 2, 4], 4); -- 3</span><br><span class="line">-- 数组元素的以第一个和最后一个元素。</span><br><span class="line">SELECT length([12,3,4,4,4]);</span><br><span class="line">SELECT array(12,22,31)[1];</span><br><span class="line">WITH</span><br><span class="line">	[23,43,565,2,32,34] AS arr</span><br><span class="line">SELECT</span><br><span class="line">	arr[1], -- 去除数组中的第一个元素</span><br><span class="line">	arr[length(arr)]; -- 提取元素中的最后一个元素</span><br><span class="line">	</span><br><span class="line">-- 10.计算数组中包含指定元素的个数</span><br><span class="line">-- countEqual(arr, x) 返回数组中等于x的元素的个数。相当于arrayCount（elem - &gt; elem = x，arr）。</span><br><span class="line">-- 注意：null值将作为单独的元素值处理。</span><br><span class="line">SELECT</span><br><span class="line">	countEqual([1, 2, 2, 2, 3, 4], 2), -- 3</span><br><span class="line">	countEqual([1, 2, NULL, NULL], NULL); -- 2</span><br><span class="line">	</span><br><span class="line">-- 11.arrayEnumerate(arr)  返回 Array [1, 2, 3, ..., length (arr) ] 此功能通常与ARRAY JOIN一起使用。它允许在应用ARRAY JOIN后为每个数组计算一次。</span><br><span class="line">SELECT arrayEnumerate([1,20,20,3]); -- [1,2,3,4]</span><br><span class="line">SELECT arrayEnumerate(array(11,20,13)); -- [1,2,3]</span><br><span class="line">SELECT arrayEnumerate(array(11,20,13,NULL)); -- [1,2,3,4] 注意：null也算是一个元素。</span><br><span class="line">--arrayEnumerateUniq(arr)  返回与源数组大小相同的数组，其中每个元素表示与其下标对应的源数组元素在源数组中出现的次数</span><br><span class="line">SELECT arrayEnumerateUniq([1,1,2,2]); -- [1,2]</span><br><span class="line">-- 12.删除数组的元素</span><br><span class="line">-- arrayPopBack(array) 删除数组array的最后一项</span><br><span class="line">SELECT arrayPopBack(array(1,2,3,0)) AS res; -- [1,2,3]</span><br><span class="line">-- arrayPopFront(array) 从数组中删除第一项</span><br><span class="line">SELECT arrayPopFront(array(0,1,2,3)) AS res; -- [1,2,3]</span><br><span class="line">-- 13.添加数组的元素 arrayPushFront(array, single_value) single_value是单个值</span><br><span class="line">SELECT arrayPushBack([1,2,3], 0) AS res; --  [1,2,3,0]</span><br><span class="line">SELECT arrayPushFront([1,2,3], 0) AS res; --  [0,1,2,3]</span><br><span class="line">-- 14.更改数组的长度  arrayResize(arr, size[, extender])</span><br><span class="line">-- 如果arr的长度 &gt; size,则会对arr截取size的长度；</span><br><span class="line">-- 如果arr的长度 &lt; size，则其余位置用对应数据类型的默认值填充。</span><br><span class="line">-- 注意：extender含义是扩展元素的值。如果没有指定extender，则默认按照对应的数据类型的默认值进行赋值。否则按照extender进行填充。</span><br><span class="line">SELECT arrayResize([1,2,3], 5); -- [1,2,3,0,0]</span><br><span class="line">SELECT arrayResize([1,2,3], 2); -- [1,2]</span><br><span class="line">SELECT arrayResize([1,2,3], 3); -- [1,2,3]</span><br><span class="line">--↓↓↓ RuntimeException: Parse exception: ByteFragment&#123;[[[1,2],[3,4],[5,6],[],[]]], start=0, len=25&#125;</span><br><span class="line">SELECT arrayResize([array(1,2),array(3,4),array(5,6)], 5);</span><br><span class="line">SELECT arrayResize([1,2,3], 5, 12); -- [1,2,3,12,12]</span><br><span class="line">SELECT arrayResize([&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;], 5); -- [&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;,&#x27;&#x27;,&#x27;&#x27;]</span><br><span class="line">SELECT arrayResize([&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;], 5, &#x27;default&#x27;); -- [&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;,&#x27;default&#x27;,&#x27;default&#x27;]</span><br><span class="line">-- 15.截取数组的部分元素，得到一个新的子数组</span><br><span class="line">-- arraySlice(array, offset[, length])</span><br><span class="line">--	解释：</span><br><span class="line">--	array: 数组，</span><br><span class="line">--	offset – 数组的偏移。正值表示左侧的偏移量，负值表示右侧的缩进值。数组下标从1开始。</span><br><span class="line">--	length - 子数组的长度。如果指定负值，则该函数返回[offset，array_length - length。如果省略该值，则该函数返回[offset，the_end_of_array]。</span><br><span class="line">SELECT </span><br><span class="line">	arraySlice([1,2,3,4,5,6], 0, 3), -- 无返回值</span><br><span class="line">	arraySlice([1,2,NULL,5,6], 1, 3), -- [1,2,0]</span><br><span class="line">	arraySlice([&#x27;one&#x27;,&#x27;two&#x27;,NULL], 1, 3), -- [&#x27;one&#x27;,&#x27;two&#x27;,&#x27;&#x27;]</span><br><span class="line"> 	arraySlice([1,2,3,4,5,6], 1, 3); -- [1,2,3]</span><br><span class="line"> 	 	</span><br><span class="line">-- 16.数组排序：arraySort([func,] arr, ……)</span><br><span class="line">-- 注意：如果在字符串数组中，&#x27;&#x27;和NULL是需要特别对待的，&#x27;&#x27;需要放在最前面，而NULL则是按顺序存放到最后的。</span><br><span class="line">-- arraySort是高阶函数。您可以将lambda函数作为第一个参数传递给它。在这种情况下，排序顺序由lambda函数的调用结果决定。</span><br><span class="line">SELECT </span><br><span class="line">	arraySort([&#x27;a&#x27;,&#x27;&#x27;,NULL,&#x27;c&#x27;,&#x27;b&#x27;]) AS hasNullempty1, --[&#x27;&#x27;,&#x27;a&#x27;,&#x27;b&#x27;,&#x27;c&#x27;,&#x27;&#x27;]  (第一个是&#x27;&#x27;,最后一个&#x27;&#x27;起始是NULL)</span><br><span class="line">	arraySort(array(&#x27;ac&#x27;,&#x27;ab&#x27;,&#x27;bc&#x27;,&#x27;ad&#x27;,NULL)) AS hasNull, -- [&#x27;ab&#x27;,&#x27;ac&#x27;,&#x27;ad&#x27;,&#x27;bc&#x27;,&#x27;&#x27;]</span><br><span class="line">	arraySort(array(&#x27;ac&#x27;,&#x27;&#x27;,&#x27;ab&#x27;,NULL,&#x27;bc&#x27;,&#x27;ad&#x27;,NULL)) AS hasNullempty2, -- [&#x27;&#x27;,&#x27;ab&#x27;,&#x27;ac&#x27;,&#x27;ad&#x27;,&#x27;bc&#x27;,&#x27;&#x27;,&#x27;&#x27;]</span><br><span class="line">	arraySort([5,4,3,2,1]) AS numSorted,-- [1,2,3,4,5] (数字排序)</span><br><span class="line">	arraySort([&#x27;ca&#x27;,&#x27;bb&#x27;,&#x27;ac&#x27;]) AS strSorted;-- [&#x27;ac&#x27;,&#x27;bb&#x27;,&#x27;ca&#x27;] (字符串排序)</span><br><span class="line">SELECT </span><br><span class="line">	arraySort([NULL, 1, 3, NULL, 2]) AS sortedArr, -- [1,2,3,0,0]</span><br><span class="line">	arrayReverse(sortedArr) AS reverseSortdArr;-- [0,0,3,2,1]</span><br><span class="line">-- 下面这种排序的实质，正数转成负数，再在数学上比较升序排序。</span><br><span class="line">SELECT arraySort(x -&gt; -x, [1,2,3]) as res; -- [3,2,1] 降序：（高阶函数用法）</span><br><span class="line">SELECT arraySort((x) -&gt; -x, [1,2,3]) as res; -- [3,2,1] 降序：（高阶函数用法）</span><br><span class="line">SELECT arraySort(x -&gt; x, [5,4,3,1,2,3]) as res; -- [1,2,3,3,4,5] 升序：（高阶函数用法）</span><br><span class="line">SELECT arraySort((x) -&gt; x, [5,4,3,1,2,3]) as res; -- [1,2,3,3,4,5] 升序：（高阶函数用法）</span><br><span class="line">-- arraySort(lambda, arr1, arr2) </span><br><span class="line">SELECT arraySort((x, y) -&gt; y, [&#x27;hello&#x27;, &#x27;world&#x27;], [2, 1]) as res; -- [&#x27;world&#x27;,&#x27;hello&#x27;]</span><br><span class="line">SELECT arraySort((x, y) -&gt; -y, [0, 1, 2], [1, 2, 3]) as res; -- [2,1,0]</span><br><span class="line">-- 再次提醒：NULL, NaN, Inf的排序顺序：</span><br><span class="line">--	含义：</span><br><span class="line">--	    -Inf 是数组中的第一个。</span><br><span class="line">--	    NULL 是数组中的最后一个。</span><br><span class="line">--	    NaN  在NULL的前面。</span><br><span class="line">--	    Inf  在NaN的前面。</span><br><span class="line">-- 出现异常：RuntimeException: Parse exception: </span><br><span class="line">-- 	ByteFragment&#123;[[-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]], start=0, len=37&#125;</span><br><span class="line">SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);</span><br><span class="line">-- 17.数组翻转：arrayReverse([func,] arr, ……)</span><br><span class="line">-- 如果是NULL的话在排序的过程中，根据数组的数据类型进行默认值填充。</span><br><span class="line">SELECT</span><br><span class="line">	arrayReverse(array(&#x27;a&#x27;,&#x27;b&#x27;,&#x27;c&#x27;,NULL)) AS hasOneNull, -- [&#x27;&#x27;,&#x27;c&#x27;,&#x27;b&#x27;,&#x27;a&#x27;]</span><br><span class="line">	arrayReverse(array(&#x27;ac&#x27;,&#x27;ab&#x27;,&#x27;bc&#x27;,&#x27;ad&#x27;,NULL)) AS hasNull, -- [&#x27;&#x27;,&#x27;ad&#x27;,&#x27;bc&#x27;,&#x27;ab&#x27;,&#x27;ac&#x27;]</span><br><span class="line">	--网格视图： [&#x27;[NULL]&#x27;,&#x27;ad&#x27;,&#x27;bc&#x27;,&#x27;&#x27;,&#x27;ab&#x27;,&#x27;[NULL]&#x27;,&#x27;&#x27;,&#x27;ac&#x27;]；文本视图 ：[&#x27;&#x27;,&#x27;ad&#x27;,&#x27;bc&#x27;,&#x27;&#x27;,&#x27;ab&#x27;,&#x27;&#x27;,&#x27;&#x27;,&#x27;ac&#x27;]</span><br><span class="line">	arrayReverse(array(&#x27;ac&#x27;,&#x27;&#x27;,NULL,&#x27;ab&#x27;,&#x27;&#x27;,&#x27;bc&#x27;,&#x27;ad&#x27;,NULL)) AS hasNullEmpty, </span><br><span class="line">	arrayReverse(array(NULL, 3, NULL, 2, 1)),-- [1,2,0,3,0]</span><br><span class="line">	arrayReverse([1,2,3,4]);-- [4,3,2,1]</span><br><span class="line">-- 18.数组排序并翻转：arraySort([func,] arr, ...)</span><br><span class="line">SELECT arrayReverseSort([1, 3, 3, 0]); -- [3,3,1,0]</span><br><span class="line">SELECT arrayReverseSort([&#x27;hello&#x27;, &#x27;world&#x27;, &#x27;!&#x27;]); -- [&#x27;world&#x27;,&#x27;hello&#x27;,&#x27;!&#x27;]</span><br><span class="line">--RuntimeException: Parse exception: ByteFragment&#123;[[inf,3,2,1,-4,-inf,nan,nan,NULL,NULL]], start=0, len=37&#125;</span><br><span class="line">SELECT arrayReverseSort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]) as res;-- [inf,3,2,1,-4,-inf,nan,nan,NULL,NULL]</span><br><span class="line">-- 下面的执行顺序为：</span><br><span class="line">--    1.首先，根据lambda函数的调用结果对源数组（[1, 2, 3]）进行排序。 结果是[3, 2, 1]。</span><br><span class="line">--    2.反转上一步获得的数组。 所以，最终的结果是[1, 2, 3]。</span><br><span class="line">SELECT arrayReverseSort((x) -&gt; -x, [1, 2, 3]) as res; -- [1,2,3]</span><br><span class="line">SELECT arrayReverseSort((x) -&gt; x, [1, 2, 3]) as res; -- [1,2,3]</span><br><span class="line">-- 下面的执行顺序为：</span><br><span class="line">--    1.首先，根据lambda函数的调用结果对源数组（[&#x27;hello&#x27;，&#x27;world&#x27;]）进行排序。 其中，在第二个数组（[2,1]）中定义了源数组中相应元素的排序键。 所以，排序结果[&#x27;world&#x27;，&#x27;hello&#x27;]。</span><br><span class="line">--    2.反转上一步骤中获得的排序数组。 所以，最终的结果是[&#x27;hello&#x27;，&#x27;world&#x27;]。</span><br><span class="line">SELECT arrayReverseSort((x, y) -&gt; y, [&#x27;hello&#x27;, &#x27;world&#x27;], [2, 1]) as res;-- [&#x27;hello&#x27;,&#x27;world&#x27;]</span><br><span class="line">SELECT arrayReverseSort((x, y) -&gt; -y, [&#x27;hello&#x27;, &#x27;world&#x27;], [2, 1]) as res;-- [&#x27;world&#x27;,&#x27;hello&#x27;]</span><br><span class="line">SELECT arrayReverseSort((x, y) -&gt; x, [&#x27;hello&#x27;, &#x27;world&#x27;], [2, 1]) as res;-- [&#x27;world&#x27;,&#x27;hello&#x27;]</span><br><span class="line">--出现异常：Illegal type String of argument </span><br><span class="line">--SELECT arrayReverseSort((x, y) -&gt; -x, [&#x27;hello&#x27;, &#x27;world&#x27;], [2, 1]) as res;</span><br><span class="line">SELECT arrayReverseSort((x, y) -&gt; x, [&#x27;hello&#x27;, &#x27;world&#x27;], [1, 2]) as res;-- [&#x27;world&#x27;,&#x27;hello&#x27;]</span><br><span class="line">-- 19.统计数组中不重复元素的个数。arrayUniq(arr,……)</span><br><span class="line">-- ① 如果传递一个参数，则计算数组中不同元素的数量。</span><br><span class="line">-- ② 如果传递了多个参数，则它计算多个数组中相应位置的不同元素元组的数量</span><br><span class="line">SELECT</span><br><span class="line">	arrayUniq([1,2,3]), -- 3</span><br><span class="line">	arrayUniq([1,2,2,2,3]); -- 3</span><br><span class="line">SELECT</span><br><span class="line">	arrayUniq([1,2,3],[2,3,4]),</span><br><span class="line">	arrayUniq([1,2,2],[1,3,3]);</span><br><span class="line">-- 20.数组的特殊功能：arrayJoin(arr)  这是一个非常有用的函数。</span><br><span class="line">-- 解释：此函数将数组作为参数，并将该行在结果集中复制数组元素个数</span><br><span class="line">SELECT arrayJoin([1, 2, 3] AS src) AS dst, &#x27;Hello&#x27;, src;</span><br><span class="line"> -- 每个元素扩大两倍；</span><br><span class="line">SELECT arrayJoin([1,2,3]) * 2;</span><br><span class="line">SELECT arrayJoin([-1,-2,0,1,2]) * 2;</span><br><span class="line">--出现异常： Illegal types Array(UInt8) and Array(UInt8) of arguments of function multiply</span><br><span class="line">--SELECT multiply(array(1,2,3), 2); </span><br><span class="line">SELECT multiply(arrayJoin([-1,-2,0,1,2]), 2);</span><br><span class="line"> -- 每个元素缩小两倍</span><br><span class="line">SELECT arrayJoin([-4,-2,0,2,4]) / 2;</span><br><span class="line">SELECT divide(arrayJoin([-4,-2,0,2,4]) , 2);</span><br><span class="line"> </span><br><span class="line">-- 21.arrayDifference(arr)</span><br><span class="line">-- 返回一个数组，其中包含所有相邻元素对之间的差值</span><br><span class="line">SELECT arrayDifference([1,2,3,4]);-- [0,1,1,1]</span><br><span class="line">SELECT arrayDifference([1,3,10,50]);-- [0,2,7,40]</span><br><span class="line"> </span><br><span class="line">-- 22. arrayDistinct(arr)返回一个包含所有数组中不同元素的数组.</span><br><span class="line">-- 类似于java的Set集合，对list集合进行去重。</span><br><span class="line">SELECT arrayDistinct(array(1,2,3,4,4,4)); -- [1,2,3,4]</span><br><span class="line">SELECT arrayDistinct([1,2,2,3,4,2,2,5,4,5]); -- [1,2,3,4,5]</span><br><span class="line">SELECT arrayDistinct(array(0,1,NULL,3,4,4,4)); -- [0,1,3,4]</span><br><span class="line">-- 数组去重统计元素个数</span><br><span class="line">SELECT uniq(arrayJoin([1,2,3,6,3])); -- 4 表示数组去重后元素的个数</span><br><span class="line">SELECT uniqArray([1,2,3,4,1,2,3,4]); -- 4 表示数组去重后元素的个数</span><br><span class="line">-- 数组元素累计</span><br><span class="line">SELECT sumArray([1,2,3,4,5]);-- 15</span><br><span class="line">SELECT sum(arraySum([1,2,3,4,5])); -- 15</span><br><span class="line"> </span><br><span class="line">-- 23. arrayEnumerateDense(arr) 返回与源数组大小相同的数组，指示每个元素首次出现在源数组中的位置</span><br><span class="line">SELECT </span><br><span class="line">	arrayEnumerateDense([10,20,20,10,30]) AS numArrEnumDense,-- [1,2,2,1,3]</span><br><span class="line">	-- [1,1,2,3,4,1,3,5,5]</span><br><span class="line">	arrayEnumerateDense([10,10,2,12,3,10,12,NULL,NULL]) as arrEnumDenseHasNull,</span><br><span class="line">	-- [1,2,1,1,2,3]</span><br><span class="line">	arrayEnumerateDense([10,20,10,10,20,30]) AS arrEnumDese2;</span><br><span class="line">-- 24. arrayIntersect(arr,……) 返回所有数组元素的交集。</span><br><span class="line">-- 如果arr的数目只有一个，则返回它本身；如果有多个数组,则返回所有数组中元素的交集。</span><br><span class="line">SELECT</span><br><span class="line">	-- 注意：最后得到的数组元素的顺序。（有什么影响吗？）</span><br><span class="line">	arrayIntersect([&#x27;one&#x27;,&#x27;two&#x27;],[&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;]) as uniStrArr1, -- [&#x27;two&#x27;,&#x27;one&#x27;]</span><br><span class="line">	arrayIntersect([&#x27;aaa&#x27;,&#x27;bbb&#x27;],[&#x27;bbb&#x27;,&#x27;aaa&#x27;,&#x27;three&#x27;]) as uniStrArr2, -- [&#x27;bbb&#x27;,&#x27;aaa&#x27;]</span><br><span class="line">	arrayIntersect([1,2],[1,2,3]) as uniArr1, -- [1,2]</span><br><span class="line">	arrayIntersect([1,2],[1,2,3],[2,3,4],[2,3,4]) as uniArr2; -- 2</span><br><span class="line">SELECT </span><br><span class="line">	arrayIntersect([1,2], [3,4]), -- []</span><br><span class="line">	arrayIntersect([1,2]);-- [1,2]</span><br><span class="line">-- 25.arrayReduce(agg_func, arr1, ...)</span><br><span class="line">-- agg_func 为聚合函数，传入到数组当中。</span><br><span class="line">-- 将聚合函数应用于数组并返回其结果.如果聚合函数具有多个参数，则此函数可应用于相同大小的多个数组。</span><br><span class="line">SELECT</span><br><span class="line">	arrayReduce(&#x27;max&#x27;, [1,2,3]) AS minNum,--最大值 3</span><br><span class="line">	arrayReduce(&#x27;min&#x27;, [1,2,3]) AS maxNum,--最小值 1</span><br><span class="line">	arrayReduce(&#x27;sum&#x27;, [1,2,3]) AS sumNum;--求和 6</span><br><span class="line">-- 十三、 字符串查分合并函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 字符串拆分合并函数</span><br><span class="line">-- 1.splitByChar(separator, s) 将字符串以‘separator’拆分成多个子串。</span><br><span class="line">-- ‘separator’必须为仅包含一个字符的字符串常量。 返回拆分后的子串的数组。</span><br><span class="line">--  如果分隔符出现在字符串的开头或结尾，或者如果有多个连续的分隔符，则将在对应位置填充空的子串。</span><br><span class="line">SELECT splitByChar(&#x27;,&#x27;, &#x27;hello,world!&#x27;); -- [&#x27;hello&#x27;,&#x27;world!&#x27;]</span><br><span class="line">--下面异常：Illegal separator for function splitByChar. Must be exactly one byte.</span><br><span class="line">--SELECT splitByChar(&#x27;or&#x27;, &#x27;hello,world!&#x27;);</span><br><span class="line">-- 2.splitByString(separator, s)</span><br><span class="line">-- 与上面相同，但它使用多个字符的字符串作为分隔符。 该字符串必须为非空</span><br><span class="line">SELECT splitByString(&#x27;or&#x27;,&#x27;goodorniceorgreat&#x27;); -- [&#x27;good&#x27;,&#x27;nice&#x27;,&#x27;great&#x27;]</span><br><span class="line">-- 3.alphaTokens(s)  从范围a-z和A-Z中选择连续字节的子字符串。返回子字符串数组</span><br><span class="line">SELECT alphaTokens(&#x27;abca1abc&#x27;); -- [&#x27;abca&#x27;,&#x27;abc&#x27;]</span><br><span class="line">SELECT alphaTokens(&#x27;abc1232abc2wer3rtty&#x27;); -- [&#x27;abc&#x27;,&#x27;abc&#x27;,&#x27;wer&#x27;,&#x27;rtty&#x27;]</span><br><span class="line">-- 4.数组元素合并函数：arrayStringConcat(arr[, sparator])</span><br><span class="line">-- 使用separator将数组中列出的字符串拼接起来。</span><br><span class="line">-- ‘separator’是一个可选参数：一个常量字符串，默认情况下设置为空字符串。 返回拼接后的字符串</span><br><span class="line">SELECT arrayStringConcat([1,2,3], &#x27;-&#x27;); -- 出现异常，要求数组必须是字符串string类型的元素</span><br><span class="line">SELECT arrayStringConcat([&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;]); -- onetwothree</span><br><span class="line">SELECT arrayStringConcat([&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;], &#x27;-&#x27;); -- one-two-three</span><br><span class="line">SELECT arrayStringConcat([&#x27;one&#x27;,&#x27;two&#x27;,&#x27;three&#x27;,&#x27;&#x27;], &#x27;-&#x27;);--  one-two-three- 注意：NULL不能存在arr中</span><br><span class="line"> </span><br><span class="line">--十四、位操作符</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 位操作符</span><br><span class="line">--位操作函数适用于UInt8，UInt16，UInt32，UInt64，Int8，Int16，Int32，Int64，Float32或Float64中的任何类型。</span><br><span class="line">--结果类型是一个整数，其位数等于其参数的最大位。</span><br><span class="line">--如果至少有一个参数为有符数字，则结果为有符数字。如果参数是浮点数，则将其强制转换为Int64。</span><br><span class="line">SELECT</span><br><span class="line">	bitAnd(1,0), -- 0</span><br><span class="line">	bitAnd(1,1), -- 1</span><br><span class="line">	bitAnd(1,2), -- 0</span><br><span class="line">	bitAnd(-1,0), -- 0</span><br><span class="line">	bitAnd(-1,-2), -- -2</span><br><span class="line">	bitAnd(-10,-1), -- -10</span><br><span class="line">	bitOr(1,2), -- 3</span><br><span class="line">	bitOr(1,0), -- 1</span><br><span class="line">	bitOr(2,0), -- 2</span><br><span class="line">	bitOr(0,2); -- 2</span><br><span class="line">SELECT bitXor(1, 2), bitXor(20, 15), bitNot(2);-- 3 27 253</span><br><span class="line"> </span><br><span class="line">--十五、Hash函数：可以用于将元素不可逆的伪随机打乱。</span><br><span class="line">-- 注意：伪随机！</span><br><span class="line">SELECT </span><br><span class="line">	-- 计算字符串的MD5值。（ 如果您不需要一定使用MD5，请使用‘sipHash64’函数。）</span><br><span class="line">	halfMD5(&#x27;HELLO WORLD!&#x27;),</span><br><span class="line">	halfMD5(12);</span><br><span class="line">SELECT </span><br><span class="line">	MD5(&#x27;drew-zero,78967&#x27;);</span><br><span class="line">SELECT </span><br><span class="line">	-- 为任何类型的整数计算32位的哈希。 这是相对高效的非加密Hash函数</span><br><span class="line">	intHash32(1221232132132) AS intHash32,</span><br><span class="line">	-- 推荐：从任何类型的整数计算64位哈希码。 它的工作速度比intHash32函数快。</span><br><span class="line">	intHash64(1221232132132) AS intHash64,</span><br><span class="line">	-- 计算任意数量字符串的CityHash64或使用特定实现的Hash函数计算任意数量其他类型的Hash。</span><br><span class="line">	cityHash64(&#x27;username&#x27;) AS cityHash64,</span><br><span class="line">	-- 1.使用sha1或者sha224加密的话，只能用于字符串</span><br><span class="line">	-- 2.字符串 需使用单引号。</span><br><span class="line">	SHA1(&#x27;1232131&#x27;) AS sha1,</span><br><span class="line">	SHA224(&#x27;1232131&#x27;) AS sha224,</span><br><span class="line">	SHA256(&#x27;DREW-ZERO&#x27;) AS sha256;</span><br><span class="line">-- URLHash(url[, N]) 一种快速的非加密哈希函数，用于规范化的从URL获得的字符串</span><br><span class="line">--  从一个字符串计算一个哈希，如果结尾存在尾随符号/，？或#则忽略。 URLHash（s，N） </span><br><span class="line">-- 计算URL层次结构中字符串到N级别的哈希值，如果末尾存在尾随符号/，？或#则忽略。 URL的层级与URLHierarchy中的层级相同</span><br><span class="line">-- 用处：此函数被用于Yandex.Metrica。</span><br><span class="line">SELECT</span><br><span class="line">	URLHash(&#x27;www.baidu.com&#x27;), 	 -- 11390370829909720855</span><br><span class="line">	URLHash(&#x27;www.baidu.com&#x27;, 0), -- 11390370829909720855</span><br><span class="line">	-- </span><br><span class="line">	URLHash(&#x27;www.baidu.com&#x27;, 1); -- 11160318154034397263</span><br><span class="line">-- farmHash64(s)  计算字符串的FarmHash64。 接受一个String类型的参数。返回UInt64。</span><br><span class="line">SELECT farmHash64(&#x27;www.runoob.com&#x27;); -- 6668483584160323388</span><br><span class="line">-- javaHash(s) 计算字符串的JavaHash。 接受一个String类型的参数。返回Int32。</span><br><span class="line">SELECT javaHash(&#x27;www.baidu.com&#x27;); -- 270263191</span><br><span class="line">-- hiveHash(s) 计算字符串的HiveHash。 接受一个String类型的参数。返回Int32。 与JavaHash相同，但不会返回负数</span><br><span class="line">SELECT hiveHash(&#x27;www.baidu.com&#x27;); -- 270263191</span><br><span class="line"> </span><br><span class="line">--十六、随机函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; 随机函数</span><br><span class="line">-- 解释：随机函数使用非加密方式生成【伪随机】数字。</span><br><span class="line">-- ① 所有随机函数都只接受一个参数或不接受任何参数。</span><br><span class="line">-- ② 您可以向它传递任何类型的参数，但传递的参数将不会使用在任何随机数生成过程中。</span><br><span class="line">-- ③  此参数的唯一目的是防止公共子表达式消除，以便在相同的查询中使用相同的随机函数生成不同的随机数</span><br><span class="line">-- rand() 函数：返回一个UInt32类型的随机数字，所有UInt32类型的数字被生成的概率均相等。</span><br><span class="line">-- rand64() 函数：返回一个UInt64类型的随机数字，所有UInt64类型的数字被生成的概率均相等。</span><br><span class="line">-- randConstant() 函数：返回一个UInt32类型的随机数字，该函数不同之处在于仅为每个数据块参数一个随机数。</span><br><span class="line">SELECT </span><br><span class="line">	rand(), -- 1751687411</span><br><span class="line">	rand(10), -- 1124981728</span><br><span class="line">	rand64(),</span><br><span class="line">	rand64(10),</span><br><span class="line">	randConstant(),</span><br><span class="line">	randConstant();</span><br><span class="line"> </span><br><span class="line">-- 十七、编码函数：</span><br><span class="line">-- hex(), unhex(), UUIDStringToNum(str), UUIDNumToString(str),bitmaskToList(num) ...</span><br><span class="line">-- 1.hex函数编码</span><br><span class="line">SELECT</span><br><span class="line">	-- 68656C6C6F20776F726C64212C68656C6C6F20636C69636B686F757365</span><br><span class="line">	hex(&#x27;hello world!,hello clickhouse&#x27;) AS hexStr,</span><br><span class="line">	hex(now()) AS hexDatetime, -- 5D414BA2</span><br><span class="line">	hex(toDate(now())) AS hexDate; --46BC</span><br><span class="line">	</span><br><span class="line">-- 2.接受包含36个字符的字符串，格式为“123e4567-e89b-12d3-a456-426655440000”，并将其转化为FixedString（16）返回</span><br><span class="line">SELECT UUIDStringToNum(&#x27;123e4567-e89b-12d3-a456-426655440000&#x27;);</span><br><span class="line">-- 3. 接受一个整数。返回一个UInt64类型数组，其中包含一组2的幂列表，其列表中的所有值相加等于这个整数。数组中的数字按升序排列。</span><br><span class="line">-- bitmaskToArray(num)</span><br><span class="line">SELECT bitmaskToArray(10); -- [2,8]</span><br><span class="line">SELECT bitmaskToArray(100); -- [4,32,64]</span><br><span class="line">-- 4.接受一个整数。返回一个字符串，其中包含一组2的幂列表，其列表中的所有值相加等于这个整数。列表使用逗号分割，按升序排列。</span><br><span class="line">-- bitmaskToList(num) </span><br><span class="line">SELECT bitmaskToList(10); -- 2,8</span><br><span class="line">SELECT bitmaskToList(100); -- 4,32,64</span><br><span class="line">SELECT bitmaskToList(0); -- &#x27;&#x27; 空字符串</span><br><span class="line"> </span><br><span class="line">--十八、UUID函数</span><br><span class="line">---&gt;&gt;&gt;&gt;&gt;&gt; UUID函数</span><br><span class="line">-- 1.generateUUIDv4()  返回 UUID类型的值。</span><br><span class="line">SELECT generateUUIDv4() as randomUUID; -- 随机生成一个UUIDv4的字符串（b6940dfe-0dc9-4788-bac7-319d13235a2e）</span><br><span class="line">SELECT replaceAll(toString(generateUUIDv4()), &#x27;-&#x27;, &#x27;&#x27;) AS replaceUUID; -- 9d1947ea4fcf450da5391feb6142cab6</span><br><span class="line">-- 2.toUUID(s) 将string类型的值 转换成UUID类型的值</span><br><span class="line">SELECT toUUID(&#x27;61f0c404-5cb3-11e7-907b-a6006ad3dba0&#x27;) AS uuid;</span><br><span class="line">-- 3.接受一个String类型的值，其中包含36个字符且格式为xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx，</span><br><span class="line">--   将其转换为UUID的数值并以FixedString(16)将其返回。</span><br><span class="line">SELECT</span><br><span class="line">    &#x27;612f3c40-5d3b-217e-707b-6a546a3d7b29&#x27; AS uuid, -- 612f3c40-5d3b-217e-707b-6a546a3d7b29</span><br><span class="line">    UUIDStringToNum(uuid) AS bytes; --a/&lt;@];!~p&#123;jTj=&#123;)</span><br><span class="line">    </span><br><span class="line">-- 4. UUIDNumToString() 接受一个FixedString(16)类型的值，返回其对应的String表现形式。</span><br><span class="line">SELECT &#x27;a/&lt;@];!~p&#123;jTj=&#123;)&#x27; AS bytes,</span><br><span class="line">    UUIDNumToString(toFixedString(bytes, 16)) AS uuid;</span><br><span class="line">--- 二十、 URL函数：所有这些功能都不遵循RFC。它们被最大程度简化以提高性能。</span><br><span class="line">--- 什么事RFC？</span><br><span class="line">----  Request For Comments（RFC），是一系列以编号排定的文件。文件收集了有关互联网相关信息，以及UNIX和互联网社区的软件文件。</span><br><span class="line">-- 1. 截取函数：如果URL中没有要截取的内容则返回空字符串。</span><br><span class="line">SELECT protocol(&#x27;http://www.baidu.com&#x27;);-- http</span><br><span class="line">SELECT protocol(&#x27;https://www.baidu.com&#x27;);-- https</span><br><span class="line">SELECT protocol(&#x27;www.baidu.com&#x27;);-- &#x27;&#x27;</span><br><span class="line">-- 获取域名。</span><br><span class="line">SELECT domain(&#x27;http://www.baidu.com&#x27;); -- www.baidu.com</span><br><span class="line">SELECT domain(&#x27;https://www.google.com.cn&#x27;); -- www.google.com.cn</span><br><span class="line">-- 返回域名并删除第一个‘www.’</span><br><span class="line">SELECT domainWithoutWWW(&#x27;http://www.baidu.com&#x27;);-- baidu.com</span><br><span class="line">SELECT domainWithoutWWW(&#x27;www.baidu.com&#x27;);-- &#x27;&#x27;</span><br><span class="line">-- 返回顶级域名。例如：.ru</span><br><span class="line">SELECT topLevelDomain(&#x27;http://www.runoob.com.cn&#x27;); -- cn</span><br><span class="line">SELECT topLevelDomain(&#x27;https://www.huse.edn&#x27;); -- edu</span><br><span class="line">-- 返回“第一个有效子域名”</span><br><span class="line">--	如果顶级域名为‘com’，‘net’，‘org’或者‘co’则第一个有效子域名为二级域名。否则则返回三级域名</span><br><span class="line">SELECT firstSignificantSubdomain(&#x27;https://news.yandex.com.tr/&#x27;); -- yandex</span><br><span class="line">-- 返回包含顶级域名与第一个有效子域名之间的内容(参阅上面内容)</span><br><span class="line">SELECT cutToFirstSignificantSubdomain(&#x27;https://news.yandex.com.tr/&#x27;); -- yandex.com.tr</span><br><span class="line">-- 返回URL路径</span><br><span class="line">SELECT path(&#x27;https://blog.csdn.net/u012111465/article/details/85250030&#x27;);-- /u012111465/article/details/85250030</span><br><span class="line">-- 与上面相同，但包括请求参数和fragment。</span><br><span class="line">SELECT pathFull(&#x27;https://clickhouse.yandex/#quick-start&#x27;); -- /#quick-start</span><br><span class="line">-- 返回请求参数。例如：page=1&amp;lr=213。请求参数不包含问号已经# 以及# 之后所有的内容。</span><br><span class="line">SELECT queryString(&#x27;http://www.baidu.com/?page=1&amp;lr=234&#x27;); -- page=1&amp;lr=234 (根据？确定)</span><br><span class="line">SELECT queryString(&#x27;http://www.baidu.com/page=1&amp;lr=234&#x27;); -- &#x27;&#x27;</span><br><span class="line">-- 返回URL的fragment标识。fragment不包含#。</span><br><span class="line">SELECT fragment(&#x27;https://clickhouse.yandex/#quick-start&#x27;); -- quick-start</span><br><span class="line">-- 返回请求参数和fragment标识。例如：page=1#29390。</span><br><span class="line">SELECT queryStringAndFragment(&#x27;https://www.baidu.com/s?ie=utf-8&amp;rsv_sug7=100#ei-ai&#x27;); -- ie=utf-8&amp;rsv_sug7=100#ei-ai</span><br><span class="line"> </span><br><span class="line">-- 2. 删除URL中的部分内容 (如果URL中不包含指定的部分，则URL不变。)</span><br><span class="line">SELECT cutWWW(&#x27;www.baidu.com&#x27;);-- www.baidu.com</span><br><span class="line">SELECT cutWWW(&#x27;https://www.baidu.com&#x27;);-- www.baidu.com</span><br><span class="line">SELECT cutWWW(&#x27;https://www.baidu.com&#x27;);-- www.baidu.com</span><br><span class="line">-- 删除请求参数</span><br><span class="line">SELECT cutQueryString(&#x27;http://www.baidu.com/1?page=1&#x27;); -- http://www.baidu.com/1</span><br><span class="line">-- 删除fragment标识。#同样也会被删除。</span><br><span class="line">SELECT cutFragment(&#x27;http://www.baidu.com/#quick-demo&#x27;); -- http://www.baidu.com/</span><br><span class="line">-- 删除请求参数以及fragment标识。问号以及#也会被删除。</span><br><span class="line">SELECT cutQueryStringAndFragment(&#x27;http://www.baidu.com/1?page=23#we&#x27;); -- http://www.baidu.com/1</span><br><span class="line">-- cutURLParameter(URL, name) 删除URL中名称为‘name’的参数。下面例子中的参数是：&amp;之后，resv,name</span><br><span class="line">SELECT cutURLParameter(&#x27;http://www.baidu.com/1?page=1#erre&amp;resv=23&amp;name=user&#x27;,&#x27;resv&#x27;);</span><br><span class="line"> </span><br><span class="line">--二十一、IP函数</span><br><span class="line"> </span><br><span class="line">--二十二、条件函数</span><br><span class="line">SELECT IF(12 &gt; 10 , 12, 20);</span><br><span class="line">SELECT 12 &gt; 10 ? 12 : 10;</span><br><span class="line">SELECT if(greater(12, 10), 12, 10);</span><br><span class="line"> </span><br><span class="line">--二十三、操作符函数替换</span><br><span class="line">-- clickhouse自带的计算操作符函数（对接mybatis的时候不用将“&lt;”之类的符号转换成 “age1 &lt;![CDATA[ &lt; ]] 2&gt;”）</span><br><span class="line">-- 1.等于(注意函数名称的大小，严格区分大小写)</span><br><span class="line">SELECT</span><br><span class="line">	equals(&#x27;hello&#x27;,&#x27;hello&#x27;), -- 1</span><br><span class="line">	equals(&#x27;ab&#x27;,&#x27;ba&#x27;); -- 0</span><br><span class="line">-- 2.不等于</span><br><span class="line">SELECT </span><br><span class="line">	notEquals(&#x27;a&#x27;,&#x27;b&#x27;), -- 1</span><br><span class="line">	notEquals(&#x27;a&#x27;,&#x27;a&#x27;), -- 0</span><br><span class="line">	notEquals(12, 12), -- 1</span><br><span class="line">	notEquals(12, 1010); -- 0</span><br><span class="line">-- 3.大于( 如果前者大于后者，则返回1；否则返回0)</span><br><span class="line">SELECT</span><br><span class="line">	greater(12, 10), -- 1 </span><br><span class="line">	greater(10, 12), -- 0</span><br><span class="line">	greater(12, 12), -- 0</span><br><span class="line">	greater(&#x27;b&#x27;,&#x27;a&#x27;), -- 1</span><br><span class="line">	greater(&#x27;a&#x27;,&#x27;b&#x27;); -- 0</span><br><span class="line">-- 3.1 扩展：提取两者中最大的值</span><br><span class="line">SELECT greatest(12,11); -- 12</span><br><span class="line">-- 4.小于（如果前者小于后者，则返回1；否则返回0）</span><br><span class="line">SELECT less(12,23); -- 1</span><br><span class="line">SELECT less(120,23); -- 0</span><br><span class="line">-- 5.大于或等于</span><br><span class="line">SELECT greaterOrEquals(12,12); -- 1</span><br><span class="line">SELECT greaterOrEquals(120,12); -- 1</span><br><span class="line">-- 6.小于或等于</span><br><span class="line">SELECT lessOrEquals(12,12); -- 1</span><br><span class="line">SELECT lessOrEquals(12,129); -- 1</span><br><span class="line">-- ===== String操作</span><br><span class="line">-- *. a LIKE s</span><br><span class="line">SELECT like(&#x27;a&#x27;, &#x27;abcd&#x27;); -- 0</span><br><span class="line">SELECT like(&#x27;a&#x27;, &#x27;a&#x27;); -- 1</span><br></pre></td></tr></table></figure>
</div>
  </div>
  
  
    
    <div class='footer'>
      
      
      
      
    </div>
  
  
    


  <div class='article-meta' id="bottom">
    <div class='new-meta-box'>
      
        
          
  
  <div class="new-meta-item meta-tags"><a class="tag" href="/tags/ClickHouse/" rel="nofollow"><i class="fa-solid fa-hashtag fa-fw" aria-hidden="true"></i><p>ClickHouse</p></a></div>
  <span hidden itemprop="keywords">ClickHouse</span>


        
      
        
          


<div class="new-meta-item browse">
  <a class='notlink'>
    <p>
      <i class="fa-solid fa-eye fa-fw" aria-hidden="true"></i>
      
      <span id="lc-pv" data-title="ClickHouse常用函数" data-path="/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html">
        <span id='number'><i class="fa-solid fa-loader fa-spin fa-fw" aria-hidden="true"></i></span>
      </span>
      
      <span>次浏览</span>
    </p>
  </a>
</div>


        
      
        
          
  <div class="new-meta-item share -mob-share-list">
  <div class="-mob-share-list share-body">
    
      
        <a class="-mob-share-qq" title="" rel="external nofollow noopener noreferrer noopener"
          
          target="_blank" href="http://connect.qq.com/widget/shareqq/index.html?url=https://blog.bosong.online/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html&title=ClickHouse常用函数 - 星空&summary=前言近期项目引入了Clickhouse进行数据计算与展示，今天突然我们的PM同学来问我一个问题，怎么快速获取这十个品牌中GMV各自排名前三的SPU数据。当时想了想，一般情况下这种需求都是使用代码实现的，简直So easy，但是一旦写代码的话就没法快速获取并且以后类似的需求也没法快速响应了。
然后想到了咱们使用的是Clickhouse数据库，它自带很多函数，在经过一段时间的查询以后，发现有一个函数还是比较好用的，正好解决这种典型的分组取TOP的问题，这个函数就是groupArray(x)，可以查看以下sql，看它是怎么使用的：
12345678910111213select * from  (select b.bid,b.brand as brand, c[1] as top1,c[2] as top2,c[3] as top3,groupArray(3)(b.spu_name) as c,groupArray(10)(b.gmv) as d,d[1]+d[2]+d[3]+d[4]+d[5]+d[6]+d[7]+d[8]+d[9]+d[10] as gmvsum  from (        select spu_id,spu_name,gmv,us,brand,bid    from spu        where platform_id = 2    and c2_id = 3305    and bid in (1234, 5678, 1903, 41313, 3145, 555, 111, 444, 222, 444)          and multiSearchAny(spu_name, [&#x27;精酿啤酒&#x27;,&#x27;啤酒精酿&#x27;])          and not multiSearchAny(spu_name, [&#x27;饮料&#x27;])          and effective_date between &#x27;2020-09-30&#x27; and &#x27;2021-02-28&#x27;    order by gmv desc)      b group by b.bid,b.brand order by bid) c order by c.gmvsum desc;

Clickhouse的简单介绍ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)；
Mysql等数据库是将数据以行的形式来进行存储，它是标准的行式数据库，但是在列式数据库中数据以列的形式来进行存储;以列的形式来存储数据，它还是有不少优点的，主要是：

绝大多数是读请求
数据以相当大的批次(&gt; 1000行)更新，而不是单行更新;或者根本没有更新。
已添加到数据库的数据不能修改。
对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
宽表，即每个表包含着大量的列
查询相对较少(通常每台服务器每秒查询数百次或更少)
对于简单查询，允许延迟大约50毫秒
列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
事务不是必须的
对数据一致性要求低
每个查询有一个大表。除了他以外，其他的都很小。
查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

可以根据官方文档来看一下OLAP的列式数据库查询为什么比行式数据库查询效率要高：行式数据库

列式数据库

一般情况下，在数据计算时只需要抽取其中一部分列，所以使用列式数据库相比较来说会快速的查找到需要的列，相比行式数据库来说减少了大量的IO。当然还有一些其他方面的优势，可以参考官方的文档。
"
          
          >
          
            <img src="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/logo/128/qq.png" class="lazyload" data-srcset="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/logo/128/qq.png" srcset="">
          
        </a>
      
    
      
        <a class="-mob-share-qzone" title="" rel="external nofollow noopener noreferrer noopener"
          
          target="_blank" href="https://sns.qzone.qq.com/cgi-bin/qzshare/cgi_qzshare_onekey?url=https://blog.bosong.online/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html&title=ClickHouse常用函数 - 星空&summary=前言近期项目引入了Clickhouse进行数据计算与展示，今天突然我们的PM同学来问我一个问题，怎么快速获取这十个品牌中GMV各自排名前三的SPU数据。当时想了想，一般情况下这种需求都是使用代码实现的，简直So easy，但是一旦写代码的话就没法快速获取并且以后类似的需求也没法快速响应了。
然后想到了咱们使用的是Clickhouse数据库，它自带很多函数，在经过一段时间的查询以后，发现有一个函数还是比较好用的，正好解决这种典型的分组取TOP的问题，这个函数就是groupArray(x)，可以查看以下sql，看它是怎么使用的：
12345678910111213select * from  (select b.bid,b.brand as brand, c[1] as top1,c[2] as top2,c[3] as top3,groupArray(3)(b.spu_name) as c,groupArray(10)(b.gmv) as d,d[1]+d[2]+d[3]+d[4]+d[5]+d[6]+d[7]+d[8]+d[9]+d[10] as gmvsum  from (        select spu_id,spu_name,gmv,us,brand,bid    from spu        where platform_id = 2    and c2_id = 3305    and bid in (1234, 5678, 1903, 41313, 3145, 555, 111, 444, 222, 444)          and multiSearchAny(spu_name, [&#x27;精酿啤酒&#x27;,&#x27;啤酒精酿&#x27;])          and not multiSearchAny(spu_name, [&#x27;饮料&#x27;])          and effective_date between &#x27;2020-09-30&#x27; and &#x27;2021-02-28&#x27;    order by gmv desc)      b group by b.bid,b.brand order by bid) c order by c.gmvsum desc;

Clickhouse的简单介绍ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)；
Mysql等数据库是将数据以行的形式来进行存储，它是标准的行式数据库，但是在列式数据库中数据以列的形式来进行存储;以列的形式来存储数据，它还是有不少优点的，主要是：

绝大多数是读请求
数据以相当大的批次(&gt; 1000行)更新，而不是单行更新;或者根本没有更新。
已添加到数据库的数据不能修改。
对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
宽表，即每个表包含着大量的列
查询相对较少(通常每台服务器每秒查询数百次或更少)
对于简单查询，允许延迟大约50毫秒
列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
事务不是必须的
对数据一致性要求低
每个查询有一个大表。除了他以外，其他的都很小。
查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

可以根据官方文档来看一下OLAP的列式数据库查询为什么比行式数据库查询效率要高：行式数据库

列式数据库

一般情况下，在数据计算时只需要抽取其中一部分列，所以使用列式数据库相比较来说会快速的查找到需要的列，相比行式数据库来说减少了大量的IO。当然还有一些其他方面的优势，可以参考官方的文档。
"
          
          >
          
            <img src="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/logo/128/qzone.png" class="lazyload" data-srcset="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/logo/128/qzone.png" srcset="">
          
        </a>
      
    
      
        <a class="-mob-share-weibo" title="" rel="external nofollow noopener noreferrer noopener"
          
          target="_blank" href="http://service.weibo.com/share/share.php?url=https://blog.bosong.online/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html&title=ClickHouse常用函数 - 星空&summary=前言近期项目引入了Clickhouse进行数据计算与展示，今天突然我们的PM同学来问我一个问题，怎么快速获取这十个品牌中GMV各自排名前三的SPU数据。当时想了想，一般情况下这种需求都是使用代码实现的，简直So easy，但是一旦写代码的话就没法快速获取并且以后类似的需求也没法快速响应了。
然后想到了咱们使用的是Clickhouse数据库，它自带很多函数，在经过一段时间的查询以后，发现有一个函数还是比较好用的，正好解决这种典型的分组取TOP的问题，这个函数就是groupArray(x)，可以查看以下sql，看它是怎么使用的：
12345678910111213select * from  (select b.bid,b.brand as brand, c[1] as top1,c[2] as top2,c[3] as top3,groupArray(3)(b.spu_name) as c,groupArray(10)(b.gmv) as d,d[1]+d[2]+d[3]+d[4]+d[5]+d[6]+d[7]+d[8]+d[9]+d[10] as gmvsum  from (        select spu_id,spu_name,gmv,us,brand,bid    from spu        where platform_id = 2    and c2_id = 3305    and bid in (1234, 5678, 1903, 41313, 3145, 555, 111, 444, 222, 444)          and multiSearchAny(spu_name, [&#x27;精酿啤酒&#x27;,&#x27;啤酒精酿&#x27;])          and not multiSearchAny(spu_name, [&#x27;饮料&#x27;])          and effective_date between &#x27;2020-09-30&#x27; and &#x27;2021-02-28&#x27;    order by gmv desc)      b group by b.bid,b.brand order by bid) c order by c.gmvsum desc;

Clickhouse的简单介绍ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)；
Mysql等数据库是将数据以行的形式来进行存储，它是标准的行式数据库，但是在列式数据库中数据以列的形式来进行存储;以列的形式来存储数据，它还是有不少优点的，主要是：

绝大多数是读请求
数据以相当大的批次(&gt; 1000行)更新，而不是单行更新;或者根本没有更新。
已添加到数据库的数据不能修改。
对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
宽表，即每个表包含着大量的列
查询相对较少(通常每台服务器每秒查询数百次或更少)
对于简单查询，允许延迟大约50毫秒
列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
事务不是必须的
对数据一致性要求低
每个查询有一个大表。除了他以外，其他的都很小。
查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

可以根据官方文档来看一下OLAP的列式数据库查询为什么比行式数据库查询效率要高：行式数据库

列式数据库

一般情况下，在数据计算时只需要抽取其中一部分列，所以使用列式数据库相比较来说会快速的查找到需要的列，相比行式数据库来说减少了大量的IO。当然还有一些其他方面的优势，可以参考官方的文档。
"
          
          >
          
            <img src="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/logo/128/weibo.png" class="lazyload" data-srcset="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/media/org.volantis/logo/128/weibo.png" srcset="">
          
        </a>
      
    
  </div>
</div>



        
      
    </div>
    <!-- Custom Files bottomMeta begin -->
    
    <!-- Custom Files bottomMeta end -->
  </div>


  
  

  
    <div class="prev-next">
      
        <a class='prev' href='/ubuntu%E9%85%8D%E7%BD%AEclash.html'>
          <p class='title'><i class="fa-solid fa-chevron-left" aria-hidden="true"></i>ubuntu配置clash</p>
          <p class='content'>前言作为一名软件开发攻城狮，日常佛跳墙是有必要的。近期讲win电脑上安装了utuntu系统准备作为开发的主力系统。本次记录一下如何在ubuntu （20.04）安装 clash作为访问googl...</p>
        </a>
      
      
        <a class='next' href='/MBP%E5%A4%96%E6%8E%A5%E9%94%AE%E7%9B%98%E8%AE%BE%E7%BD%AE.html'>
          <p class='title'>MBP外接键盘设置<i class="fa-solid fa-chevron-right" aria-hidden="true"></i></p>
          <p class='content'>

</p>
        </a>
      
    </div>
  
  <!-- Custom Files postEnd begin-->
  
  <!-- Custom Files postEnd end-->
</article>


  


  <article class="post white-box shadow floatable blur" id="comments">
    <span hidden>
      <meta itemprop="discussionUrl" content="/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html#comments">
    </span>
    <p ct><i class='fa-solid fa-comments'></i> 评论</p>
    

    <div id="layoutHelper-comments"></div>

  </article>






</div>
<aside id='l_side' itemscope itemtype="http://schema.org/WPSideBar">
  
  
    
    



  <section class="widget toc-wrapper shadow floatable blur desktop mobile pjax" id="toc-div" >
    
  <header>
    
      <i class="fa-solid fa-list fa-fw" aria-hidden="true"></i><span class='name'>本文目录</span>
    
  </header>


    <div class='content'>
        <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%89%8D%E8%A8%80"><span class="toc-text">前言</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Clickhouse%E7%9A%84%E7%AE%80%E5%8D%95%E4%BB%8B%E7%BB%8D"><span class="toc-text">Clickhouse的简单介绍</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Clickhouse%E5%87%BD%E6%95%B0%E6%8C%87%E5%8D%97"><span class="toc-text">Clickhouse函数指南</span></a></li></ol>
    </div>
  </section>

  


<!-- 没有 pjax 占位会报错 万恶的 pjax -->

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>

  <div class="pjax">
    <!-- pjax占位 -->
  </div>


  <!-- Custom Files side begin -->
  
  <!-- Custom Files side end -->
</aside>



          <!--此文件用来存放一些不方便取值的变量-->
<!--思路大概是将值藏到重加载的区域内-->

<pjax>
<script>
  window.pdata={}
  pdata.ispage=true;
  pdata.commentPath="";
  pdata.commentPlaceholder="";
  pdata.commentConfig={};
  //  see: /layout/_partial/scripts/_ctrl/coverCtrl.ejs
  
    // header
    var l_header=document.getElementById("l_header");
    
    l_header.classList.add("show");
    
    
      // cover
      var cover_wrapper=document.querySelector('#l_cover .cover-wrapper');
      var scroll_down=document.getElementById('scroll-down');
      cover_wrapper.id="none";
      cover_wrapper.style.display="none";
      scroll_down.style.display="none";
    
  
</script>
</pjax>
        </div>
        
  
  <footer class="footer clearfix"  itemscope itemtype="http://schema.org/WPFooter">
    <br><br>
    
      
        <div class="aplayer-container">
          


        </div>
      
    
      
        <br>
        <div class="social-wrapper" itemprop="about" itemscope itemtype="http://schema.org/Thing">
          
            
          
            
          
            
          
        </div>
      
    
      
        <div class='copyright'>
        <p><a href="/">© 2017 – 2022 <i class="fa-solid fa-heart"></i> jack</a></p>

        </div>
      
    
      
        
          <div><p><a href="https://beian.miit.gov.cn/" target="_blank">皖ICP备-17017700号-4|</a><img src="https://imagecdn.bosong.online/image/beian.png" class="lazyload" data-srcset="https://imagecdn.bosong.online/image/beian.png" srcset=""></img><a href="http://www.beian.gov.cn/portal/registerSystemInfo?recordcode=11011202001448" target="_blank">京公网安备11011202001448号|</a></p>
</div>
        
      
    
    <!-- Custom Files footer begin-->
    
    <!-- Custom Files footer end-->
  </footer>


        <a id="s-top" class="fa-solid fa-arrow-up fa-fw" href="/" onclick="return false;" title="top"></a>
      </div>
    </div>
    <div>
      <script>
  /******************** volantis.dom ********************************/
  // 页面选择器 将dom对象缓存起来 see: /source/js/app.js etc.
  volantis.dom.bodyAnchor = volantis.dom.$(document.getElementById("safearea")); // 页面主体
  volantis.dom.topBtn = volantis.dom.$(document.getElementById('s-top')); // 向上
  volantis.dom.wrapper = volantis.dom.$(document.getElementById('wrapper')); // 整个导航栏
  volantis.dom.coverAnchor = volantis.dom.$(document.querySelector('#l_cover .cover-wrapper')); // 1个
  volantis.dom.switcher = volantis.dom.$(document.querySelector('#l_header .switcher .s-search')); // 搜索按钮   移动端 1个
  volantis.dom.header = volantis.dom.$(document.getElementById('l_header')); // 移动端导航栏
  volantis.dom.search = volantis.dom.$(document.querySelector('#l_header .m_search')); // 搜索框 桌面端 移动端 1个
  volantis.dom.mPhoneList = volantis.dom.$(document.querySelectorAll('#l_header .m-phone .list-v')); //  手机端 子菜单 多个
</script>

<script>
  
  volantis.css("https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/@fortawesome/fontawesome-free/css/all.min.css");
  
  
  
</script>

<!-- required -->


<!-- internal -->

<script src="/js/app.e546c981.js"></script>






<!-- rightmenu要在darkmode之前（ToggleButton） darkmode要在comments之前（volantis.dark.push）-->

  <script>
const rootElement = document.documentElement;
const darkModeStorageKey = "color-scheme";
const rootElementDarkModeAttributeName = "color-scheme";
const setLS = (k, v) => {
    localStorage.setItem(k, v);
};
const removeLS = (k) => {
    localStorage.removeItem(k);
};
const getLS = (k) => {
    return localStorage.getItem(k);
};
const getModeFromCSSMediaQuery = () => {
  return window.matchMedia("(prefers-color-scheme: dark)").matches
    ? "dark"
    : "light";
};
const resetRootDarkModeAttributeAndLS = () => {
  rootElement.removeAttribute(rootElementDarkModeAttributeName);
  removeLS(darkModeStorageKey);
};
const validColorModeKeys = {
  dark: true,
  light: true,
};
const applyCustomDarkModeSettings = (mode) => {
  const currentSetting = mode || getLS(darkModeStorageKey);
  getCustomDarkMode();
  if (currentSetting === getModeFromCSSMediaQuery()) {
    resetRootDarkModeAttributeAndLS();
  } else if (validColorModeKeys[currentSetting]) {
    rootElement.setAttribute(rootElementDarkModeAttributeName, currentSetting);
  } else {
    resetRootDarkModeAttributeAndLS();
  }
};
const invertDarkModeObj = {
  dark: "light",
  light: "dark",
};
/**
 * get target mode
 */
 const getCustomDarkMode = () => {
  let currentSetting = getLS(darkModeStorageKey);
  if (validColorModeKeys[currentSetting]) {
    currentSetting = invertDarkModeObj[currentSetting];
  } else if (currentSetting === null) {
    currentSetting = invertDarkModeObj[getModeFromCSSMediaQuery()];
  } else {
    return;
  }
  if(currentSetting=="dark"){
    volantis.dark.mode="light";
  }else{
    volantis.dark.mode="dark";
  }
  // console.log(volantis.dark.mode)
};
const toggleCustomDarkMode = () => {
  let currentSetting = getLS(darkModeStorageKey);
  if (validColorModeKeys[currentSetting]) {
    currentSetting = invertDarkModeObj[currentSetting];
  } else if (currentSetting === null) {
    currentSetting = invertDarkModeObj[getModeFromCSSMediaQuery()];
  } else {
    return;
  }
  setLS(darkModeStorageKey, currentSetting);
  return currentSetting;
};
/**
 * 暗黑模式触发器
 */
volantis.dark.toggle=()=>{
  const mode = toggleCustomDarkMode();
  applyCustomDarkModeSettings(mode);
  // 使用 volantis.dark.push 方法传入volantis.dark.toggle回调函数 参见layout/_partial/scripts/global.ejs
  volantis.dark.method.toggle.start();
}
/**
 * bind event for toggle button
 */

function bindToggleButton() {
  var btn= document.querySelectorAll("#wrapper .toggle-mode-btn,#rightmenu-wrapper .toggle-mode-btn")
  btn.forEach(function (e) {
    volantis.dom.$(e).on('click',volantis.dark.toggle);
  })
}
applyCustomDarkModeSettings();
document.addEventListener("DOMContentLoaded", ()=>{
  volantis.requestAnimationFrame(bindToggleButton)
});
volantis.pjax.push(bindToggleButton);

const darkModelListeners={
  dark:(mediaQueryList )=>{
    if(mediaQueryList.matches){
      volantis.dark.mode = "dark";
    }
    volantis.dark.method.toggle.start();
  },
  light:(mediaQueryList)=>{
    if(mediaQueryList.matches){
      volantis.dark.mode = "light";
    }
    volantis.dark.method.toggle.start();
  }
}
window.matchMedia('(prefers-color-scheme: dark)').addListener(darkModelListeners.dark)
window.matchMedia('(prefers-color-scheme: light)').addListener(darkModelListeners.light)
</script>




<script>
  function loadIssuesJS() {
    
      const sites_api = document.getElementById('sites-api');
      if (sites_api != undefined && typeof SitesJS === 'undefined') {
        volantis.js("/js/plugins/tags/sites.76bf19b8.js")
      }
    
    
      const friends_api = document.getElementById('friends-api');
      if (friends_api != undefined && typeof FriendsJS === 'undefined') {
        volantis.js("/js/plugins/tags/friends.f372da57.js")
      }
    
    
      const contributors_api = document.getElementById('contributors-api');
      if (contributors_api != undefined && typeof ContributorsJS === 'undefined') {
        volantis.js("/js/plugins/tags/contributors.aec80453.js")
      }
    
  };
  loadIssuesJS()
  volantis.pjax.push(()=>{
    loadIssuesJS();
  })

</script>




  <script defer src="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/vanilla-lazyload/dist/lazyload.min.js"></script>
<script>
  // https://www.npmjs.com/package/vanilla-lazyload
  // Set the options globally
  // to make LazyLoad self-initialize
  window.lazyLoadOptions = {
    elements_selector: ".lazyload",
    threshold: 0
  };
  // Listen to the initialization event
  // and get the instance of LazyLoad
  window.addEventListener(
    "LazyLoad::Initialized",
    function (event) {
      window.lazyLoadInstance = event.detail.instance;
    },
    false
  );
  document.addEventListener('DOMContentLoaded', function () {
    lazyLoadInstance.update();
  });
  document.addEventListener('pjax:complete', function () {
    lazyLoadInstance.update();
  });
</script>




  

<script>
  window.FPConfig = {
	delay: 0,
	ignoreKeywords: ["#"],
	maxRPS: 6,
	hoverDelay: 0
  };
</script>
<script defer src="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/flying-pages/flying-pages.min.js"></script>





   <script type="text/javascript">
  function pjax_scrollrebeal() {
    ScrollReveal().reveal("#l_main .reveal", {
      distance: "32px",
      duration: "800",
      interval: "20",
      scale: "1",
      easing: "ease-out",
    });
  }
  function init_scrollrebeal() {
    if (typeof ScrollReveal == "undefined") {
      volantis.requestAnimationFrame(init_scrollrebeal);
    } else {
      pjax_scrollrebeal();
    }
  }
  volantis.js("https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/scrollreveal/dist/scrollreveal.min.js");
  document.addEventListener("DOMContentLoaded", init_scrollrebeal);
  volantis.pjax.push(
    pjax_scrollrebeal,
    "pjax_scrollrebeal",
    (setRequestAnimationFrame = false)
  );
</script>






      <script>
  volantis.layoutHelper("comments",`<div id="waline"><i class="fa-solid fa-cog fa-spin fa-fw fa-2x"></i></div>`)
  
  function pjax_waline() {
    if(!document.querySelector("#waline"))return;
    let locale = {};
    let path = pdata.commentPath;
    let pagePlaceholder = pdata.commentPlaceholder || "快来评论吧~";
    if (path.length == 0) {
      let defaultPath = '';
      path = defaultPath || decodeURI(window.location.pathname);
    }
    if(pagePlaceholder.length != 0) {
      locale.placeholder = pagePlaceholder;
    }

    try {
      Waline.init(Object.assign(Object.assign({"js":"https://npm.elemecdn.com/@waline/client@v2/dist/waline.js","css":"https://npm.elemecdn.com/@waline/client@v2/dist/waline.css","path":null,"placeholder":"快来评论吧~","serverURL":"https://waline.songbo.fun","imageUploader":{"api":null,"token":null,"resp":null},"pageview":true,"comment":true,"meta":["nick","mail","link"],"requiredFields":["nick","mail"],"avatar":"robohash","avatarCDN":null,"pageSize":10,"lang":"zh-CN","visitor":true}, {
        el: '#waline',
        path: path,
        // https://github.com/volantis-x/hexo-theme-volantis/issues/713
        
        locale,
      }),pdata.commentConfig));
    } catch (error) {
      alert(`Waline ${error}`)
    }
    fancybox_waline()
  }

  function fancybox_waline() {
    if(typeof VolantisFancyBox === "undefined") {
      const checkFancyBox = setInterval(() => {
        if(typeof VolantisFancyBox === "undefined") return;
        clearInterval(checkFancyBox);
        VolantisFancyBox.bind('#waline .wl-content img:not(.wl-emoji)');
      })
    } else {
      VolantisFancyBox.bind('#waline .wl-content img:not(.wl-emoji)');
    }
  }

  volantis.css('https://npm.elemecdn.com/@waline/client@v2/dist/waline.css')
  volantis.js('https://npm.elemecdn.com/@waline/client@v2/dist/waline.js').then(pjax_waline)
  volantis.pjax.push(pjax_waline);
</script>
    





<!-- optional -->

  <script>
  const SearchServiceDataPathRoot = ("/" || "/").endsWith("/") ?
    "/" || "/" :
    "//" || "/";
  const SearchServiceDataPath = SearchServiceDataPathRoot + "content.json";

  function loadSearchScript() {
    // see: layout/_partial/scripts/_ctrl/cdnCtrl.ejs
    return volantis.js("/js/search/hexo.0e52f222.js");
  }

  function loadSearchService() {
    loadSearchScript();
    document.querySelectorAll(".input.u-search-input").forEach((e) => {
      e.removeEventListener("focus", loadSearchService, false);
    });

    document.querySelectorAll(".u-search-form").forEach((e) => {
      e.addEventListener("submit", (event) => {
        event.preventDefault();
      }, false);
    });
  }

  // 打开并搜索 字符串 s
  function OpenSearch(s) {
    if (typeof SearchService === 'undefined')
      loadSearchScript().then(() => {
        SearchService.setQueryText(s);
        SearchService.search();
      });
    else {
      SearchService.setQueryText(s);
      SearchService.search();
    }
  }

  // 访问含有 ?s=xxx  的链接时打开搜索 // 与搜索引擎 structured data 相关: /scripts/helpers/structured-data/lib/config.js
  if (window.location.search && /^\?s=/g.test(window.location.search)) {
    let queryText = decodeURI(window.location.search)
      .replace(/\ /g, "-")
      .replace(/^\?s=/g, "");
    OpenSearch(queryText);
  }

  // 搜索输入框获取焦点时加载搜索
  document.querySelectorAll(".input.u-search-input").forEach((e) => {
    e.addEventListener("focus", loadSearchService, false);
  });
</script>







  <script>



  function pjax_highlightjs_copyCode(){
    if (!(document.querySelector(".highlight .code pre") ||
      document.querySelector(".article pre code"))) {
      return;
    }
    VolantisApp.utilCopyCode(".highlight .code pre, .article pre code")
  }
  volantis.requestAnimationFrame(pjax_highlightjs_copyCode)
  volantis.pjax.push(pjax_highlightjs_copyCode)

</script>




  <script defer>

  const LCCounter = {
    app_id: 'RUsgBcCqsPC5tWXNdV6zTrud-gzGzoHsz',
    app_key: 'dR54grixzuWsdUJ6ShA61VqK',
    custom_api_server: 'https://message.alidaodao.com',

    // 查询存储的记录
    getRecord(Counter, url, title) {
      return new Promise(function (resolve, reject) {
        Counter('get', '/classes/Counter?where=' + encodeURIComponent(JSON.stringify({url})))
          .then(resp => resp.json())
          .then(({results, code, error}) => {
            if (code === 401) {
              throw error;
            }
            if (results && results.length > 0) {
              var record = results[0];
              resolve(record);
            } else {
              Counter('post', '/classes/Counter', {url, title: title, times: 0})
                .then(resp => resp.json())
                .then((record, error) => {
                  if (error) {
                    throw error;
                  }
                  resolve(record);
                }).catch(error => {
                console.error('Failed to create', error);
                reject(error);
              });
            }
          }).catch((error) => {
          console.error('LeanCloud Counter Error:', error);
          reject(error);
        });
      })
    },

    // 发起自增请求
    increment(Counter, incrArr) {
      return new Promise(function (resolve, reject) {
        Counter('post', '/batch', {
          "requests": incrArr
        }).then((res) => {
          res = res.json();
          if (res.error) {
            throw res.error;
          }
          resolve(res);
        }).catch((error) => {
          console.error('Failed to save visitor count', error);
          reject(error);
        });
      });
    },

    // 构建自增请求体
    buildIncrement(objectId) {
      return {
        "method": "PUT",
        "path": `/1.1/classes/Counter/${ objectId }`,
        "body": {
          "times": {
            '__op': 'Increment',
            'amount': 1
          }
        }
      }
    },

    // 校验是否为有效的 UV
    validUV() {
      var key = 'LeanCloudUVTimestamp';
      var flag = localStorage.getItem(key);
      if (flag) {
        // 距离标记小于 24 小时则不计为 UV
        if (new Date().getTime() - parseInt(flag) <= 86400000) {
          return false;
        }
      }
      localStorage.setItem(key, new Date().getTime().toString());
      return true;
    },

    addCount(Counter) {
      var enableIncr = '' === 'true' && window.location.hostname !== 'localhost';
      enableIncr = true;
      var getterArr = [];
      var incrArr = [];
      // 请求 PV 并自增
      var pvCtn = document.querySelector('#lc-sv');
      if (pvCtn || enableIncr) {
        var pvGetter = this.getRecord(Counter, 'https://blog.bosong.online' + '/#lc-sv', 'Visits').then((record) => {
          incrArr.push(this.buildIncrement(record.objectId))
          var eles = document.querySelectorAll('#lc-sv #number');
          if (eles.length > 0) {
            eles.forEach((el,index,array)=>{
              el.innerText = record.times + 1;
              if (pvCtn) {
                pvCtn.style.display = 'inline';
              }
            })
          }
        });
        getterArr.push(pvGetter);
      }

      // 请求 UV 并自增
      var uvCtn = document.querySelector('#lc-uv');
      if (uvCtn || enableIncr) {
        var uvGetter = this.getRecord(Counter, 'https://blog.bosong.online' + '/#lc-uv', 'Visitors').then((record) => {
          var vuv = this.validUV();
          vuv && incrArr.push(this.buildIncrement(record.objectId))
          var eles = document.querySelectorAll('#lc-uv #number');
          if (eles.length > 0) {
            eles.forEach((el,index,array)=>{
              el.innerText = record.times + (vuv ? 1 : 0);
              if (uvCtn) {
                uvCtn.style.display = 'inline';
              }
            })
          }
        });
        getterArr.push(uvGetter);
      }

      // 请求文章的浏览数，如果是当前页面就自增
      var allPV = document.querySelectorAll('#lc-pv');
      if (allPV.length > 0 || enableIncr) {
        for (i = 0; i < allPV.length; i++) {
          let pv = allPV[i];
          let title = pv.getAttribute('data-title');
          var url = 'https://blog.bosong.online' + pv.getAttribute('data-path');
          if (url) {
            var viewGetter = this.getRecord(Counter, url, title).then((record) => {
              // 是当前页面就自增
              let curPath = window.location.pathname;
              if (curPath.includes('index.html')) {
                curPath = curPath.substring(0, curPath.lastIndexOf('index.html'));
              }
              if (pv.getAttribute('data-path') == curPath) {
                incrArr.push(this.buildIncrement(record.objectId));
              }
              if (pv) {
                var ele = pv.querySelector('#lc-pv #number');
                if (ele) {
                  if (pv.getAttribute('data-path') == curPath) {
                    ele.innerText = (record.times || 0) + 1;
                  } else {
                    ele.innerText = record.times || 0;
                  }
                  pv.style.display = 'inline';
                }
              }
            });
            getterArr.push(viewGetter);
          }
        }
      }

      // 如果启动计数自增，批量发起自增请求
      if (enableIncr) {
        Promise.all(getterArr).then(() => {
          incrArr.length > 0 && this.increment(Counter, incrArr);
        })
      }

    },


    fetchData(api_server) {
      var Counter = (method, url, data) => {
        return fetch(`${ api_server }/1.1${ url }`, {
          method,
          headers: {
            'X-LC-Id': this.app_id,
            'X-LC-Key': this.app_key,
            'Content-Type': 'application/json',
          },
          body: JSON.stringify(data)
        });
      };
      this.addCount(Counter);
    },

    refreshCounter() {
      var api_server = this.app_id.slice(-9) !== '-MdYXbMMI' ? this.custom_api_server : `https://${ this.app_id.slice(0, 8).toLowerCase() }.api.lncldglobal.com`;
      if (api_server) {
        this.fetchData(api_server);
      } else {
        fetch('https://app-router.leancloud.cn/2/route?appId=' + this.app_id)
          .then(resp => resp.json())
          .then(({api_server}) => {
            this.fetchData('https://' + api_server);
          });
      }
    }

  };

  LCCounter.refreshCounter();

  document.addEventListener('pjax:complete', function () {
    LCCounter.refreshCounter();
  });
</script>










  <script>
  function load_swiper() {
    if (!document.querySelectorAll(".swiper-container")[0]) return;
    volantis.css("https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/swiper/swiper-bundle.min.css");
    volantis.js("https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/swiper/swiper-bundle.min.js").then(() => {
      pjax_swiper();
    });
  }

  load_swiper();

  function pjax_swiper() {
    volantis.swiper = new Swiper('.swiper-container', {
      slidesPerView: 'auto',
      spaceBetween: 8,
      centeredSlides: true,
      loop: true,
      pagination: {
        el: '.swiper-pagination',
        clickable: true,
      },
      navigation: {
        nextEl: '.swiper-button-next',
        prevEl: '.swiper-button-prev',
      },
    });
  }

  volantis.pjax.push(() => {
    if (!document.querySelectorAll(".swiper-container")[0]) return;
    if (typeof volantis.swiper === "undefined") {
      load_swiper();
    } else {
      pjax_swiper();
    }
  });
</script>


<!-- pjax 标签必须存在于所有页面 否则 pjax error -->
<pjax>

</pjax>

<script>
  function listennSidebarTOC() {
    const navItems = document.querySelectorAll(".toc li");
    if (!navItems.length) return;
    let targets = []
    const sections = [...navItems].map((element) => {
      const link = element.querySelector(".toc-link");
      const target = document.getElementById(
        decodeURI(link.getAttribute("href")).replace("#", "")
      );
      targets.push(target)
      // 解除 a 标签 href 的 锚点定位, a 标签 href 的 锚点定位 会随机启用?? 产生错位???
      link.setAttribute("onclick","return false;")
      link.setAttribute("toc-action","toc-"+decodeURI(link.getAttribute("href")).replace("#", ""))
      link.setAttribute("href","/")
      // 配置 点击 触发新的锚点定位
      link.addEventListener("click", (event) => {
        event.preventDefault();
        // 这里的 addTop 是通过错位使得 toc 自动展开.
        volantis.scroll.to(target,{addTop: 5, observer:true})
        // Anchor id
        history.pushState(null, document.title, "#" + target.id);
      });
      return target;
    });

    function activateNavByIndex(target) {
      if (target.classList.contains("active-current")) return;

      document.querySelectorAll(".toc .active").forEach((element) => {
        element.classList.remove("active", "active-current");
      });
      target.classList.add("active", "active-current");
      let parent = target.parentNode;
      while (!parent.matches(".toc")) {
        if (parent.matches("li")) parent.classList.add("active");
        parent = parent.parentNode;
      }
    }

    // 方案一：
    volantis.activateNavIndex=0
    activateNavByIndex(navItems[volantis.activateNavIndex])
    volantis.scroll.push(()=>{
      if (targets[0].getBoundingClientRect().top >= 0) {
        volantis.activateNavIndex = 0
      }else if (targets[targets.length-1].getBoundingClientRect().top < 0) {
        volantis.activateNavIndex = targets.length-1
      } else {
        for (let index = 0; index < targets.length; index++) {
          const target0 = targets[index];
          const target1 = targets[(index+1)%targets.length];
          if (target0.getBoundingClientRect().top < 0&&target1.getBoundingClientRect().top >= 0) {
            volantis.activateNavIndex=index
            break;
          }
        }
      }
      activateNavByIndex(navItems[volantis.activateNavIndex])
    })

    // 方案二：
    // IntersectionObserver 不是完美精确到像素级别 也不是低延时性的
    // function findIndex(entries) {
    //   let index = 0;
    //   let entry = entries[index];
    //   if (entry.boundingClientRect.top > 0) {
    //     index = sections.indexOf(entry.target);
    //     return index === 0 ? 0 : index - 1;
    //   }
    //   for (; index < entries.length; index++) {
    //     if (entries[index].boundingClientRect.top <= 0) {
    //       entry = entries[index];
    //     } else {
    //       return sections.indexOf(entry.target);
    //     }
    //   }
    //   return sections.indexOf(entry.target);
    // }
    // function createIntersectionObserver(marginTop) {
    //   marginTop = Math.floor(marginTop + 10000);
    //   let intersectionObserver = new IntersectionObserver(
    //     (entries, observe) => {
    //       let scrollHeight = document.documentElement.scrollHeight;
    //       if (scrollHeight > marginTop) {
    //         observe.disconnect();
    //         createIntersectionObserver(scrollHeight);
    //         return;
    //       }
    //       let index = findIndex(entries);
    //       activateNavByIndex(navItems[index]);
    //     }, {
    //       rootMargin: marginTop + "px 0px -100% 0px",
    //       threshold: 0,
    //     }
    //   );
    //   sections.forEach((element) => {
    //     element && intersectionObserver.observe(element);
    //   });
    // }
    // createIntersectionObserver(document.documentElement.scrollHeight);
  }

  document.addEventListener("DOMContentLoaded", ()=>{
    volantis.requestAnimationFrame(listennSidebarTOC)
  });
  document.addEventListener("pjax:success", ()=>{
    volantis.requestAnimationFrame(listennSidebarTOC)
  });
</script>

<script>
  try {
    // https://web.dev/content-visibility/
    // https://www.caniuse.com/?search=content-visibility
    // https://infrequently.org/2020/12/content-visibility-scroll-fix/
    // https://infrequently.org/2020/12/resize-resilient-deferred-rendering/

    // 备注 目前已知的问题:
    // 动态修改导致的内容高度变化(例如评论框异步渲染的外部盒子高度变化) 无法提前获知, 进而导致的首次滚动条跳动无法去除 (wontfix) 事实上不使用 content-visibility 也会有跳动, 不过是比使用 content-visibility 跳动提前
    // scrollreveal 插件潜在问题 目前尚不明确

    let eqIsh = (a, b, fuzz = 2) => {
      return Math.abs(a - b) <= fuzz;
    };

    let rectNotEQ = (a, b) => {
      return !eqIsh(a.width, b.width) || !eqIsh(a.height, b.height);
    };

    // Keep a map of elements and the dimensions of
    // their place-holders, re-setting the element's
    // intrinsic size when we get updated measurements
    // from observers.
    let spaced = new WeakMap();

    // Only call this when known cheap, post layout
    let reserveSpace = (el, rect = el.getClientBoundingRect()) => {
      let old = spaced.get(el);
      // Set intrinsic size to prevent jumping on un-painting:
      //    https://drafts.csswg.org/css-sizing-4/#intrinsic-size-override
      if (!old || rectNotEQ(old, rect)) {
        spaced.set(el, rect);
        el.style["contain-intrinsic-size"] = `${rect.width}px ${rect.height}px`;
      }
    };

    let iObs = new IntersectionObserver(
      (entries, o) => {
        entries.forEach((entry) => {
          // We don't care if the element is intersecting or
          // has been laid out as our page structure ensures
          // they'll get the right width.
          reserveSpace(entry.target, entry.boundingClientRect);
        });
      },
      { rootMargin: "500px 0px 500px 0px" }
    );

    let rObs = new ResizeObserver((entries, o) => {
      entries.forEach((entry) => {
        reserveSpace(entry.target, entry.contentRect);
      });
    });

    let resizeResilientDeferredRendering = (Selector) => {
      let articles = document.querySelectorAll(Selector);

      if (articles.length) {
        articles.forEach((el) => {
          iObs.observe(el);
          rObs.observe(el);
        });

        // Workaround for Chrome bug, part 2.
        //
        // Re-enable browser management of rendering for the
        // first article after the first paint. Double-rAF
        // to ensure we get called after a layout.
        requestAnimationFrame(() => {
          requestAnimationFrame(() => {
            articles[0].style["content-visibility"] = "auto";
          });
        });
      }
    };

    let contentVisibilityScrollFix = () => {
      if (!("content-visibility" in document.documentElement.style)) {
        return;
      }
      resizeResilientDeferredRendering(".post-story");
    };
    contentVisibilityScrollFix();
    volantis.pjax.push(contentVisibilityScrollFix);
  } catch (error) {
    console.log(error);
  }
</script>



  <script type="application/ld+json">[{"@context":"http://schema.org","@type":"Organization","name":"星空","url":"https://blog.bosong.online/","logo":{"@type":"ImageObject","url":"https://imagecdn.bosong.online/image/google.svg","width":192,"height":192}},{"@context":"http://schema.org","@type":"Person","name":"Jack","image":{"@type":"ImageObject","url":"https://imagecdn.bosong.online/image/google.svg"},"url":"https://blog.bosong.online/","sameAs":["https://github.com/dislazy"],"description":"Jack，现居于北京朝阳，专注于Devops 技术人生 Java Golang 微服务 云计算，这是Jack的个人博客站"},{"@context":"http://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"item":{"@id":"https://blog.bosong.online/","name":"星空"}},{"@type":"ListItem","position":2,"item":{"@id":"https://blog.bosong.online/categories/ClickHouse/","name":"ClickHouse"}},{"@type":"ListItem","position":3,"item":{"@id":"https://blog.bosong.online/ClickHouse常用函数.html","name":"ClickHouse常用函数"}}]},{"@context":"http://schema.org","@type":"WebSite","name":"星空","url":"https://blog.bosong.online/","keywords":"Devops 技术人生 Java Golang 微服务 云计算","description":"Jack，现居于北京朝阳，专注于Devops 技术人生 Java Golang 微服务 云计算，这是Jack的个人博客站","author":{"@type":"Person","name":"Jack","image":{"@type":"ImageObject","url":"https://imagecdn.bosong.online/image/google.svg"},"url":"https://blog.bosong.online/","description":"Jack，现居于北京朝阳，专注于Devops 技术人生 Java Golang 微服务 云计算，这是Jack的个人博客站"},"publisher":{"@type":"Organization","name":"星空","url":"https://blog.bosong.online/","logo":{"@type":"ImageObject","url":"https://imagecdn.bosong.online/image/google.svg","width":192,"height":192}},"potentialAction":{"@type":"SearchAction","name":"Site Search","target":{"@type":"EntryPoint","urlTemplate":"https://blog.bosong.online?s={search_term_string}"},"query-input":"required name=search_term_string"}},{"@context":"http://schema.org","@type":"BlogPosting","headline":"ClickHouse常用函数","description":"前言近期项目引入了Clickhouse进行数据计算与展示，今天突然我们的PM同学来问我一个问题，怎么快速获取这十个品牌中GMV各自排名前三的SPU数据。当时想了想，一般情况下这种需求都是使用代码实现的，简直So easy，但是一旦写代码的话就没法快速获取并且以后类似的需求也没法快速响应了。\n然后想到了咱们使用的是Clickhouse数据库，它自带很多函数，在经过一段时间的查询以后，发现有一个函数还是比较好用的，正好解决这种典型的分组取TOP的问题，这个函数就是groupArray(x)，可以查看以下sql，看它是怎么使用的：\n12345678910111213select * from  (select b.bid,b.brand as brand, c[1] as top1,c[2] as top2,c[3] as top3,groupArray(3)(b.spu_name) as c,groupArray(10)(b.gmv) as d,d[1]+d[2]+d[3]+d[4]+d[5]+d[6]+d[7]+d[8]+d[9]+d[10] as gmvsum  from (        select spu_id,spu_name,gmv,us,brand,bid    from spu        where platform_id = 2    and c2_id = 3305    and bid in (1234, 5678, 1903, 41313, 3145, 555, 111, 444, 222, 444)          and multiSearchAny(spu_name, [&#x27;精酿啤酒&#x27;,&#x27;啤酒精酿&#x27;])          and not multiSearchAny(spu_name, [&#x27;饮料&#x27;])          and effective_date between &#x27;2020-09-30&#x27; and &#x27;2021-02-28&#x27;    order by gmv desc)      b group by b.bid,b.brand order by bid) c order by c.gmvsum desc;\n\nClickhouse的简单介绍ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)；\nMysql等数据库是将数据以行的形式来进行存储，它是标准的行式数据库，但是在列式数据库中数据以列的形式来进行存储;以列的形式来存储数据，它还是有不少优点的，主要是：\n\n绝大多数是读请求\n数据以相当大的批次(&gt; 1000行)更新，而不是单行更新;或者根本没有更新。\n已添加到数据库的数据不能修改。\n对于读取，从数据库中提取相当多的行，但只提取列的一小部分。\n宽表，即每个表包含着大量的列\n查询相对较少(通常每台服务器每秒查询数百次或更少)\n对于简单查询，允许延迟大约50毫秒\n列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)\n处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)\n事务不是必须的\n对数据一致性要求低\n每个查询有一个大表。除了他以外，其他的都很小。\n查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中\n\n可以根据官方文档来看一下OLAP的列式数据库查询为什么比行式数据库查询效率要高：行式数据库\n\n列式数据库\n\n一般情况下，在数据计算时只需要抽取其中一部分列，所以使用列式数据库相比较来说会快速的查找到需要的列，相比行式数据库来说减少了大量的IO。当然还有一些其他方面的优势，可以参考官方的文档。\n","inLanguage":["zh-CN","en","zh-TW","default"],"mainEntityOfPage":{"@type":"WebPage","@id":"https://blog.bosong.online/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html"},"author":{"@type":"Person","name":"Jack","image":{"@type":"ImageObject","url":"https://imagecdn.bosong.online/image/google.svg"},"url":"https://blog.bosong.online/"},"publisher":{"@type":"Organization","name":"星空","logo":{"@type":"ImageObject","url":"https://imagecdn.bosong.online/image/google.svg","width":192,"height":192}},"url":"https://blog.bosong.online/ClickHouse%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0.html","wordCount":1585,"datePublished":"2021-03-18T12:47:45.000Z","dateModified":"2022-06-02T01:05:59.611Z","articleSection":"ClickHouse","keywords":"ClickHouse","image":{"@type":"ImageObject","url":"https://imagecdn.bosong.online/image/google.svg","width":192,"height":192}}]</script>



      
        <!--
  pjax重载区域接口：
  1.  <pjax></pjax> 标签 pjax 标签必须存在于所有页面 否则 pjax error
  2.  script[data-pjax]
  3.  .pjax-reload script
  4.  .pjax
-->



<script src="https://npm.elemecdn.com/volantis-static@0.0.1649552113628/libs/pjax/pjax.min.js"></script>


<script>
    var pjax;
    document.addEventListener('DOMContentLoaded', function () {
      pjax = new Pjax({
        elements: 'a[href]:not([href^="#"]):not([href="javascript:void(0)"]):not([pjax-fancybox]):not([onclick="return false;"]):not([onclick="return!1"]):not([target="_blank"]):not([target="view_window"]):not([href$=".xml"])',
        selectors: [
          "head title",
          "head meta[name=keywords]",
          "head meta[name=description]",
          
          "#l_main",
          "#pjax-header-nav-list",
          ".pjax",
          "pjax", // <pjax></pjax> 标签
          "script[data-pjax], .pjax-reload script" // script标签添加data-pjax 或 script标签外层添加.pjax-reload 的script代码段重载
        ],
        cacheBust: false,   // url 地址追加时间戳，用以避免浏览器缓存
        timeout: 5000,
        
      });
    });

    document.addEventListener('pjax:send', function (e) {
      //window.stop(); // 相当于点击了浏览器的停止按钮

      try {
        var currentUrl = window.location.pathname;
        var targetUrl = e.triggerElement.href;
        var banUrl = [""];
        if (banUrl[0] != "") {
          banUrl.forEach(item => {
            if(currentUrl.indexOf(item) != -1 || targetUrl.indexOf(item) != -1) {
              window.location.href = targetUrl;
            }
          });
        }
      } catch (error) {}

      // 使用 volantis.pjax.send 方法传入pjax:send回调函数 参见layout/_partial/scripts/global.ejs
      volantis.pjax.method.send.start();
    });

    document.addEventListener('pjax:complete', function () {
      // 使用 volantis.pjax.push 方法传入重载函数 参见layout/_partial/scripts/global.ejs
      volantis.pjax.method.complete.start();
    });

    document.addEventListener('pjax:error', function (e) {
      if(volantis.debug) {
        console.error(e);
        console.log('pjax error: \n' + JSON.stringify(e));
      }else{
        // 使用 volantis.pjax.error 方法传入pjax:error回调函数 参见layout/_partial/scripts/global.ejs
        volantis.pjax.method.error.start();
        window.location.href = e.triggerElement.href;
      }
    });
</script>

      
    </div>
    <!-- import body_end begin-->
        <script></script>
    <!-- import body_end end-->
    <!-- Custom Files bodyEnd begin-->
    
    <!-- Custom Files bodyEnd end-->
  </body>
</html>
